artsyL

July 10, 2013

that is why I am confused. This seems like it should work

Here is the result for var_dump($input, $tags, $tag):

string(16) "tag1, tag2, tag3" array(3) { [0]=> string(4) "tag1" [1]=> string(5) " tag2" [2]=> string(5) " tag3" } string(4) "tag3"

$input and $tags are doing what is expected, but $tag does not; therefore it does not insert multiple rows later on.

July 10, 2013

I have a similar problem, but it is just different enough...

I have a tag input field that asks for comma separated input. I am able to explode this into an array. The trouble comes when I want to insert each tag into its own row. I tried to assign a value for each array item, but I can't figure that out.

Here is what I have so far: the var_dump for $tags gives the array, but $tag only gives me the last array item.

//check for tags
	if (!empty($_POST['tag'])) {
		$input = $_POST['tag'];
		$tags = explode(',', $input);
		}else { //if empty
		$tag = NULL;
		}
	if (isset ($tags)){
		foreach($tags as $tag) {
		$tag = trim($tag); 
		}
	}

July 9, 2013

Just in case someone might be interested, I thought I would share the jquery functionality I added:

I wanted certain input fields to show/hide depending on what was selected. I found the trick was to change the input to text. It is also important to set the CSS display to none.

Here is the PHP:

echo "<p>Please select collection type: <select id=selectEnum class=\"box\" type=\"text\" name=\"coll_type\">";
		$q = "SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS
			WHERE TABLE_NAME = 'collection' AND COLUMN_NAME = 'coll_type'";
		$r = mysqli_query($dbc, $q);
		
		$row = mysqli_fetch_array($r);
		
		$enumList = explode(",", str_replace("'", "", substr($row['COLUMN_TYPE'], 5, (strlen($row['COLUMN_TYPE'])-6))));
		foreach($enumList as $value)
			echo "<option value=\"$value\">$value</option>";
		
		echo "</select></p>";

Here is the Jquery:

<script>
$(document).ready(function() {
  $.enumList = {
    'select' : $([]),
    'personal' : $([]),
    'family' : $([]),
    'institutional' : $('#corporate'),
    'corporate' : $('#corporate'),
    'other' : $('#other')
  };

  $('#selectEnum').change(function() {
    // hide all
    $.each($.enumList, function() { this.hide(); });
    // show current
    $.enumList[$(this).val()].show();
  });
});
</script>

Here is the CSS:

#other {
	display:none;	
}

#corporate {
	display: none;	
}

July 6, 2013

Thank you both! That worked really well. I was over-thinking it again.

Antonio, I used my code just because I know it works (though you wouldn't think so - I was in the middle of changing some variables when I posted), but I will definitely be taking yours for a whirl. Very nicely done.

Here is the final result:

//add the location to the database.
			//geocode it first
$geoloc="$addr1, $city, $state";
$result=file_get_contents("http://maps.googleapis.com/maps/api/geocode/json?sensor=false&address=" . urlencode($geoloc) );
$geocodedinfo=json_decode($result);

//print_r($geocodedinfo);

$lat = $geocodedinfo->results[0]->geometry->location->lat;
$lng = $geocodedinfo->results[0]->geometry->location->lng;

//var_dump($lat, $lng);
			
			$q = 'INSERT INTO location (loc_address, loc_address2, loc_city, loc_state, loc_zip, loc_country, lat, lng, coll_id, country_id) VALUES
			(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
			$stmt = mysqli_prepare($dbc, $q);
			mysqli_stmt_bind_param($stmt, 'ssssssddii', $addr1, $addr2, $city, $state, $zip, $sn, $lat, $lng, $c, $coun);
			mysqli_stmt_execute($stmt);
			
			//Check the results
			if (mysqli_stmt_affected_rows($stmt)== 1){
				echo '<p>The location has been added</p>';
				$locid = mysqli_stmt_insert_id($stmt);//Get the location ID
				}else{//Error!
					$errors[] = 'the location could not be added to the database.';
				}
				
			//Close this prepared statement
			mysqli_stmt_close($stmt);

July 6, 2013

Hello again,

I'm wondering if it is possible to insert to a table and then update to that table in the same php script.

What I want to do is insert a location address, then geocode it with a location update immediately after the insert with the form action as the same page. However, I suspect that I will need to do this through a hidden submit input that takes me to another page because I have been scratching my head over it for a few hours now.

Here is the location insert

	    $q = 'INSERT INTO location (loc_address, loc_address2, loc_city, loc_state, loc_zip, loc_country, coll_id, country_id) VALUES
            (?, ?, ?, ?, ?, ?, ?, ?)';
            $stmt = mysqli_prepare($dbc, $q);
            mysqli_stmt_bind_param($stmt, 'ssssssii', $addr1, $addr2, $city, $state, $zip, $sn, $c, $coun);
            mysqli_stmt_execute($stmt);
            
            //Check the results
            if (mysqli_stmt_affected_rows($stmt)== 1){
                echo '<p>The location has been added</p>';
                $locid = mysqli_stmt_insert_id($stmt);//Get the location ID
                }else{//Error!
                    $errors[] = 'the location could not be added to the database.';
                }
                
            //Close this prepared statement
            mysqli_stmt_close($stmt);

and here is the geocoding with the update query

//geocode it
$geoloc="$addr1, $city, $state, $zip, $sn";
$georesult=file_get_contents("http://maps.googleapis.com/maps/api/geocode/json?sensor=false&address=" . urlencode($location) );
$geocodedinfo=json_decode($result);

print_r($geocodedinfo);

$lat = $geocodedinfo->results[0]->geometry->location->lat;
$lng = $geocodedinfo->results[0]->geometry->location->lng;


	$query = "UPDATE location SET loc_address='$addr1', loc_address2='$addr2', loc_city='$city', loc_state='$state',loc_zip='$zip', loc_country='$sn', lat='$lat',lng='$lng' WHERE loc_id='$locid'"; 
	$result = mysqli_query($query); 
	if ($result){
		echo "<h2><b><strong>OMG it worked!</strong></b></h2>"; 
		}else {
		echo "<p>No geocode (mope)" . mysqli_error() . "</p>"; 
		
	}

can I force the insert submit to go first and then run the update? if so, what do you suggest? I came across one article that said something about making each query into a function, but it wasn't explained very well. And do I need to create a separate pre-populated hidden form somehow for the second submit?

July 5, 2013

Wow! I managed to go a whole day without needing personal help!

Thank you for the tips. I'm working specifically on security today, so I'll probably be "seeing" you on related thread soon.

July 4, 2013

Finally got it figured out. This works! Thanks everyone!

I'm still a little worried about having no mysql_real_escape_string, but I don't know what to do about it because when it's in there it doesn't work anymore.

	//get short_name variable
			$country_id = $coun;
			$q = "SELECT short_name FROM country WHERE country_id = $country_id LIMIT 1";
			$r = mysqli_query($dbc, $q);
			$num = mysqli_num_rows($r);
			echo mysqli_num_rows($r);
			if ($num > 0) {//match was made
				//Get short_name
				$row = mysqli_fetch_array($r);
				$sn = $row['short_name'];	
			}else {
				echo '<p>no match</p>';
				}

July 4, 2013

Yes, that is what was happening, which is why I was so confused!

I took your suggestion and changed to mysql_fetch_array($r) which finally outputs something useful.

array(2) { [0]=> string(17) "Wallis and Futuna" ["short_name"]=> string(17) "Wallis and Futuna" }

Now I just have to figure out how to turn that into a variable. Should I use EXTRACT? how would I do this here?

by the way, the mysqli_real_escape_string seems to be causing some issues (mentioned above) what should I do?

Thanks!!!!

July 4, 2013

I ran this to check for errors:

$vquery = mysqli_query($q) or die(mysqli_error() . "\n<br>$q");

This is my result:

SELECT short_name FROM country WHERE country_id = 114 LIMIT 1

I ran the query in phpmyadmin and it worked: so how is this an error in PHP?

July 4, 2013

This change makes $country_id finally take on the $coun variable (the var_dump shows this), but now we are essentially back where we started (no point to having the second variable if the first will do)

$country_id = $coun;
			var_dump($coun, $country_id);
			$q = "SELECT short_name FROM country WHERE country_id = $country_id LIMIT 1";

July 4, 2013

This stops the error message, but now I'm getting a NULL row. It must not be pulling the info. When I use >0 the error message is still there.

//get short_name variable
			$country_id = mysqli_real_escape_string($coun);
			$q = "SELECT short_name FROM country WHERE country_id = $country_id LIMIT 1";
			$r = mysqli_query($dbc, $q);
			$num = mysqli_num_rows($r);
			if ($num !== 0) {//match was made
				
				//Get short_name
				$row = mysqli_fetch_assoc($r, MYSQLI_ASSOC);	
				var_dump($row);			
			}else {
				echo '<p>no match</p>';
				}

July 3, 2013

Nope, nope, nope and nope. Is this as weird as i think it is?

(single quotes):

$country_id = mysqli_real_escape_string($coun);
$q = "SELECT short_name FROM country WHERE country_id = '$country_id'";

(no quotes):

$country_id = mysqli_real_escape_string($coun);
$q = "SELECT short_name FROM country WHERE country_id = $country_id";

(double quotes):

$country_id = mysqli_real_escape_string($coun);
$q = "SELECT short_name FROM country WHERE country_id = '".$country_id."'";

(backticks):

$country_id = mysqli_real_escape_string($coun);
$q = "SELECT short_name FROM country WHERE country_id = `$country_id`";

July 3, 2013

I already tried it. Margaux has been very helpful, so I do everything s/he suggests. I was just wondering if there was something more theoretical to consider.

Still working on it.

July 3, 2013

hmm. That did not work.

this is the var_dump for the last attempt - int(246)

here is the error - no match

Why is it better to have the mysqli_real... outside of the query? Just curious.

Taking a walk. Need to clear my head.

July 3, 2013

Hi,

I'm having a similar problem and I'm not sure if it is a syntax issue or not.

What I'm trying to do is create a variable from an array that will be used in an insert statement.

a var_dump tells me that the $coun variable (country_id) is outputting correctly as an integer, so I'm not sure if there is no match being made because of the syntax or if something else is going wrong.

phpmyadmin says my query is fine.

Also, am I using the mysqli_real_escape_string correctly?

here is the code:

$q = "SELECT short_name FROM country WHERE (country_id='".mysqli_real_escape_string($coun)."')"; 
			$r = mysqli_query($dbc, $q);
			$num = mysqli_num_rows($r);
			if ($num == 1) {//match was made
				
				//Get short_name
				$row = mysqli_fetch_array($r);				
			}else {
				echo '<p>no match</p>';
				}

July 3, 2013

The $c variable is a table id # from another part of the code and it works fine in other areas (I just posted the problem area of the code). The drop down menu is also working just fine. The problem is that nothing posts to the location table and the only error code I get is "the location could not be added to the database." I assume that this means there are no errors happening when the form is validated, but that something is just not happening when it goes to post to the table.

I am wondering: since I am pulling information from the countries table (the $coun variable is the country_id field) and trying to insert it into the location table (the country_id field in "location"), is something going wrong here? the country_id fields are identical in both tables.

thanks for the tag info!

LATER: the queries are valid too. (I'm stumped).

LATER: you were right about the $c variable. It was posting as a string in an integer column in my test script. Thanks for your eagle eyes and patience.

July 3, 2013

I'm customizing the code from this chapter too, and I'm all tangled up in it and going blind. I suspect that the problem is from the insert section, but it could also be that I am trying to insert a country_id in a location table in kind of a sneaky way that I don't fully understand.

The table rows match. Do I need to do a JOIN here, maybe?

Here is the code: sorry it's all cluttered up with debugging attempts.

if (isset($_POST['submitted'])) { //Handle the form

    //validate the incoming data...
    $errors = array();
            
            
        //check for collection location
    if (!empty($_POST['loc_address'])) {
        $addr1 = trim($_POST['loc_address']);
    }else {
        $errors[] = 'no address posted';
    }
    if (!empty($_POST['loc_address2'])) {
        $addr2 = trim($_POST['loc_address2']);
    }else {
        $errors[] = 'no address2 posted';
    }
    if (!empty($_POST['loc_city'])) {
        $city = trim($_POST['loc_city']);
    }else {
        $errors[] = 'no city posted';
    }
    if (!empty($_POST['loc_state'])) {
        $state = trim($_POST['loc_state']);
    }else {
        $errors[] = 'no state posted';
    }
    if (!empty($_POST['loc_zip'])) {
        $zip = trim($_POST['loc_zip']);
    }else {
        $errors[] = 'no zip posted';
    }
    var_dump($addr1, $addr2, $city, $state, $zip);
    
    //check the country    
        if (!empty($_POST['country'])) {
        $coun = (int)trim($_POST['country']);
    }    else {
        $errors[] = 'no country posted';
    }
    var_dump($coun);
    
    if (empty($errors)){ //if everything is ok
            //add the location to the database
            $q = 'INSERT INTO location (loc_address, loc_address2, loc_city, loc_state, loc_zip, coll_id, country_id) VALUES
            (?, ?, ?, ?, ?, ?, ?)';
            $stmt = mysqli_prepare($dbc, $q);
            mysqli_stmt_bind_param($stmt, 'sssssii', $addr1, $addr2, $city, $state, $zip, $c, $coun);
            mysqli_stmt_execute($stmt);
            
            //Check the results
            if (mysqli_stmt_affected_rows($stmt)== 1){
                echo '<p>The location has been added</p>';
                $locid = mysqli_stmt_insert_id($stmt);//Get the location ID
                }else{//Error!
                    $errors[] = 'the location could not be added to the database.';
                }
                
            //Close this prepared statement
            mysqli_stmt_close($stmt);
            
        }//end of errors IF
        
    }//end of submission IF
            
    //check for any errors and print them
    if (!empty($errors) && is_array($errors)){
        echo '<h1>Error!</h1>
        <p style="font-weight:bold; color:#C00">The following error(s) occurred:<br />';
        foreach ($errors as $msg){
            echo " - $msg<br />\n";    
        }
        echo 'Please try again.</p>';
    }        
            
//Display the form    
?>    

<h1>Add a location</h1>
<form enctype="multipart/form-data" action="addCollectionTest.php" method="post">


<fieldset><legend></legend>


<p><b>(optional) Please enter the location : </b></p>
<p><b>Address 1: <input type="text" name="loc_address" size="10" maxlength="25"
value="<?php if (isset($_POST['loc_address'])) echo $_POST['loc_address']; ?>"/></b></p>

<p><b>Address 2: <input type="text" name="loc_address2" size="10" maxlength="25"
value="<?php if (isset($_POST['loc_address2'])) echo $_POST['loc_address2']; ?>"/></b></p>

<p><b>City: <input type="text" name="loc_city" size="10" maxlength="25"
value="<?php if (isset($_POST['loc_city'])) echo $_POST['loc_city']; ?>"/></b></p>

<p><b>State: <input type="text" name="loc_state" size="10" maxlength="25"
value="<?php if (isset($_POST['loc_state'])) echo $_POST['loc_state']; ?>"/></b></p>

<p><b>Postal Code: <input type="text" name="loc_zip" size="10" maxlength="10"
value="<?php if (isset($_POST['loc_zip'])) echo $_POST['loc_zip']; ?>"/></b></p>


<select name="country"><option>Select one</option>
<?php //retrieve all the countries and add to the pull-down menu

$q = "SELECT country_id, short_name FROM country";

$r = mysqli_query ($dbc, $q);
if (mysqli_num_rows($r)> 0) {
    while ($row = mysqli_fetch_array ($r, MYSQLI_NUM)) {
        echo "<option value=\"$row[0]\"";
        //Check for stickyness
        if (isset($_POST['country'])&&($_POST['country']== $row[0]))
        echo 'selected="selected"';
        echo ">$row[1]</option>\n";    
    }    
}else{
    echo '<option>Please select a country.</option>';    
}

mysqli_close($dbc);//close the database connection
?>
</select></p>
</fieldset>

<div align="center"> <input type="submit" name="submit" value="Submit" /></div>
<input type="hidden" name="submitted" value="TRUE" />

</form>

July 2, 2013

Thank you so much! I had actually tried this tutorial earlier today and I must have twisted that thing around a hundred times, but just couldn't make it cough up the array.

Woo-hoo! on to another struggle!

July 2, 2013

I have a database with many ENUM values, so I want my forms to have dynamic dropdown menus with the ENUM values. I have found some information on how to create these, but none of them use mysqli, so (as a newbie) I'm at a bit of a loss.

This is the code I have so far. I feel that I'm close, but I can't seem to make the magic happen.

<select name="coll_type"><option>Select one</option>
<?
$q ="SELECT TRIM(TRAILING ')' FROM TRIM(LEADING '(' FROM TRIM(LEADING 'enum' FROM column_type))) column_type FROM information_schema.columns WHERE table_name = 'collection' AND column_name = 'PENDING'";
$r = mysqli_query ($dbc, $q);

mysqli_fetch_array($r);
$enumList = explode(",", str_replace(array('', '', "'"), $r));
foreach($enumList as $value)
    echo "<option value=\"$value\">$value</option>";

    echo "</select>";
?>

June 26, 2013

Thank you very much! I thought I was misplacing something and just couldn't get the right combination.

June 26, 2013

Here is the original code:

//check for a description
$d = (!empty($_POST['desc'])) ? trim($_POST['desc']): NULL;

//add the decription to the database
           $q = 'INSERT INTO description (desc) VALUES
           (?)';
           $stmt = mysqli_prepare($dbc, $q);
           mysqli_stmt_bind_param($stmt, 's', $d);
           mysqli_stmt_execute($stmt);

           if (mysqli_stmt_affected_rows($stmt)== 1) {

               //print a message
               echo '<p>the description has been added</p>';
           }else{echo 'the description could not be added';
           }

<p><b>Description</b> <textarea name="desc" cols="40" rows="5">
<?php if(isset($_POST['desc'])) echo $_POST['desc']; ?></textarea> (optional)</p>

The mysql table has two fields desc_id (int, 11), and desc (varchar, 255, default NULL)

Here is the new code:

//check for a description
$d = (!empty($_POST['description'])) ? trim($_POST['description']): NULL;

//add the decription to the database
           $q = 'INSERT INTO description (description) VALUES
           (?)';
           $stmt = mysqli_prepare($dbc, $q);
           mysqli_stmt_bind_param($stmt, 's', $d);
           mysqli_stmt_execute($stmt);

           if (mysqli_stmt_affected_rows($stmt)== 1) {

               //print a message
               echo '<p>the description has been added</p>';
           }else{echo 'the description could not be added';
           }

<p><b>Description</b> <textarea name="description" cols="40" rows="5">
<?php if(isset($_POST['description'])) echo $_POST['description']; ?></textarea> (optional)</p>

The mysql table is named "description" and the field that I changed was "desc" and is now "description"

June 26, 2013

Thank you.

After I checked the sql and the auto-increment (neither was a problem), I thought I would try something new by switching everything that says "desc" to "description" (including the table fields). Now it works, but I'm not entirely sure why. Can anyone give me some insight here? I really want to understand this.

June 25, 2013

I'm having a similar issue, but I'm pretty sure that it has something to do with the textarea input.

When I attempt to insert the description into the prints table it results in a system error, but when I leave it out everything submits.

I have tried to isolate the problem by creating a separate description table and adding an error message if it doesn't submit. Anyway, I cannot figure out what is wrong after hours of trying.

Here is the isolated code:

//check for a description
$d = (!empty($_POST['desc'])) ? trim($_POST['desc']): NULL;

//add the decription to the database
           $q = 'INSERT INTO description (desc) VALUES
           (?)';
           $stmt = mysqli_prepare($dbc, $q);
           mysqli_stmt_bind_param($stmt, 's', $d);
           mysqli_stmt_execute($stmt);

           if (mysqli_stmt_affected_rows($stmt)== 1) {

               //print a message
               echo '<p>the description has been added</p>';
           }else{echo 'the description could not be added';
           }

<p><b>Description</b> <textarea name="desc" cols="40" rows="5">
<?php if(isset($_POST['desc'])) echo $_POST['desc']; ?></textarea> (optional)</p>

The mysql table has two fields desc_id (int, 11), and desc (varchar, 255, default NULL)

I would actually prefer that the description field be a text instead of varchar, so if I can get this to work, do I need to change the data type from 's' to 'b' or change the bind_param to long_data?

Any help would be much appreciated.

Sign In

artsyL

Posts

Joined

Last visited

Content Type

Forums

Posts posted by artsyL

Use Of Implode To Insert Multiple Rows Into Mysql

Use Of Implode To Insert Multiple Rows Into Mysql

Use Data Type Enum For Form Drop-Down Options

Insert And Update To Same Table On Same Page

Insert And Update To Same Table On Same Page

Database Query Syntax

Database Query Syntax

Database Query Syntax

Database Query Syntax

Database Query Syntax

Database Query Syntax

Database Query Syntax

Database Query Syntax

Database Query Syntax

Database Query Syntax

Chapter 17 - Ecommerce - Add_Book Customisation Issue

Chapter 17 - Ecommerce - Add_Book Customisation Issue

Use Data Type Enum For Form Drop-Down Options

Use Data Type Enum For Form Drop-Down Options

Editing Add-Print

Editing Add-Print

Editing Add-Print

Editing Add-Print

Browse

Activity