Need help pulling data from database (MySQL and PHP)

TeamCIT · January 8, 2010

Hey guys, I'm new here. I am trying to build a website that uses a form with a List Box to allow to user to select only one item at a time. After they select an item they are to push a (submit) button to add it to an unordered list that will be displayed is a separate div. I have a database with the items in the list box stored in it. The database has 4 fields an ID (auto-increment) Selected (will either be 1 or 0) Item Name (contains name of item) and Item address (contains address associated with item). The button is supposed to insert a 1 into the Selected field of the chosen item. Then the user will choose another item, who's selected value will also become 1. I will then try to pull all of the items with the selected value of "1" and display them in an unordered list. Any help getting this to work would be greatly appreciated. Thanks in advance.

Here is my code, if you guys notice a problem I would very much appreciate some tips:

(Note that I am connecting to the database at the top of my code and the connection is working properly so it is not included)

if($_POST['submitted'] == true) {
$destChose = $_POST['destList'];


if($destChose >= 1) {

mysql_query("INSERT INTO destinations(selected)
	 		VALUES('1')
			 WHERE id = {$destChose}");

$result = mysql_query("SELECT dest_name, dest_addr 
			FROM destinations
			WHERE selected = {1}");			



while ($row = mysql_fetch_array($result)) {

			echo "<li>";
			echo $row["dest_name"];
			echo "</li>";

}
}
}

?>


<html>
<head>
<title>Create a Tour</title>
</head>

<body>
<br />
<br />
<form name="tourCreation" action="createTour_take1.php" method="post">
Please complete this form to create your tour:
<br />
<br />
Destinations:<br />
<select name="destList" size="10">
  <option value="1">National Gallery of Art</option>
  <option value="2">National Archives & Records Administration</option>
  <option value="3">National Law Enforcement Officers Memorial</option>
  <option value="4">Kenilworth Aquatic Gardens</option>
  <option value="5">White House Park</option>
  <option value="6">Washington Photo Safari</option>
  <option value="7">Federal Bureau of Investigation</option>
  <option value="8">Franciscan Monastery - Commissariat of the Holy Land in America</option>
  <option value="9">National Portrait Gallery Special Events Department</option>
  <option value="10">Rock Creek Park</option>
  <option value="11">Anacostia Community Museum</option>
  <option value="12">National Shrine of the Immaculate Conception </option>
  <option value="13">U.S. National Arboretum</option>
  <option value="14">Union Station</option>
  
</select>
<br />
<br />
<br />
<input type="submit" name="submitted" value="Submit Feedback" />
</form>

<br />
<br />

</body>
</html>

Adam · January 8, 2010

What's the problem exactly?

TeamCIT · January 8, 2010

What's the problem exactly?

Sorry, I should have been a little more specific with my problem. I am getting an error on line 29 ( "while ($row = mysql_fetch_array($result)) {" ) when I click the submit button. The Selected value stays at 0 (when it should change to 1) and no item is displayed.

TGWSE_GY · January 8, 2010

mysql_query("INSERT INTO destinations(selected)
VALUES('1')

WHERE id = {$destChose}");

$result = mysql_query("SELECT dest_name, dest_addr

FROM destinations

WHERE selected = {1}");

should be

   mysql_query("INSERT INTO destinations(selected)
             VALUES('1')
             WHERE id = '$destChose'");
   
   $result = mysql_query("SELECT dest_name, dest_addr
            FROM destinations
            WHERE selected = '1'");

When executing a MYSQL QUERY you should always surround tables in `` and when you are saying something like "WHERE selected=1" the 1 should be surrounded in ''. Like "WHERE selected='1'"

You where you using curly brackets and they are used for control structures and function to determine the starting point and finishing point of the control structure and/or function.

Adam · January 8, 2010

the 1 should be surrounded in ''. Like "WHERE selected='1'"

Not integers, and most likely the `ID` is an integer in this case. Though the curly braces shouldn't have been around the 1.

If this doesn't fix the problem post the actual error message.

TGWSE_GY · January 8, 2010

Not integers, and most likely the `ID` is an integer in this case.

Well I always use '' around items in MYSQL QUERY's even integers just to be safe, cause I have seen some very confounded configs where it needed it. Dont ask me why cause I am not a server Admin just a Programmer LOL. But Mr Adam is right by rule of thumb you shouldnt need them with integers.

TeamCIT · January 8, 2010

mysql_query("INSERT INTO destinations(selected)
VALUES('1')

WHERE id = {$destChose}");

$result = mysql_query("SELECT dest_name, dest_addr

FROM destinations

WHERE selected = {1}");

should be
   mysql_query("INSERT INTO destinations(selected)
             VALUES('1')
             WHERE id = '$destChose'");
   
   $result = mysql_query("SELECT dest_name, dest_addr
            FROM destinations
            WHERE selected = '1'");   
When executing a MYSQL QUERY you should always surround tables in `` and when you are saying something like "WHERE selected=1" the 1 should be surrounded in ''. Like "WHERE selected='1'"

You where you using curly brackets and they are used for control structures and function to determine the starting point and finishing point of the control structure and/or function.

Thanks, this helped with the problem somewhat but not entirely. I no longer receive an error message when the Submit button is pressed, however the Selected value in my database still stays at 0 (when it should change to 1)

TGWSE_GY · January 8, 2010

ok try this

if($_POST['submitted'] == true) {
   $destChose = $_POST['destList'];
   echo $destChose;
   die();
}

and see what value the browser echo's back to you to make sure that the value is truely being designated as a 1.

TeamCIT · January 8, 2010

and see what value the browser echo's back to you to make sure that the value is truely being designated as a 1.

I tried the code you gave me, and I am getting the value I want. It is displaying a numeric value equal to it's position in the listbox, which is what I want because in the database that value is the same as that of the ID column. The problem now is making the correct comparison between the value of the selected item and the id in the database so that I may change the value in that item's Selected column (in the databse) from 0 to 1. Then I will try to select all the items with 1 in the database and echo them into an unordered list.

TGWSE_GY · January 8, 2010

Link to what you are working on, if I see it in action I may be able to help you more.

TeamCIT · January 8, 2010

Link to what you are working on, if I see it in action I may be able to help you more.

I cannot give you an actual link because I am working on it locally. I use filezilla to upload it to my school's intranet server and working on it from there. Is there anything else I can give you that would help?

TGWSE_GY · January 8, 2010

   mysql_query("INSERT INTO destinations(selected)
             VALUES('1')
             WHERE id = '$destChose'");

Do you have a column for each location in your database? Or are you trying to store all the locations in one field of the table in your DB?

TeamCIT · January 8, 2010

   mysql_query("INSERT INTO destinations(selected)
             VALUES('1')
             WHERE id = '$destChose'");
Do you have a column for each location in your database? Or are you trying to store all the locations in one field of the table in your DB?

I have a total of 6 columns in my database. It goes "ID" "Selected" "dest_name" "dest_addr" "dest_date" and "dest_description". I only want to change the value of the "Selected column, though. The others I want to keep their values.

Adam · January 8, 2010

however the Selected value in my database still stays at 0 (when it should change to 1)

Maybe wrong, but I don't see an UPDATE statement in your code, just an INSERT...? Plus you have a WHERE clause within that INSERT..

TeamCIT · January 8, 2010

Maybe wrong, but I don't see an UPDATE statement in your code, just an INSERT...? Plus you have a WHERE clause within that INSERT..

Should I be using the UPDATE instead of INSERT? That does make sense, I did not even realize the UPDATE command existed, I am fairly new to MySQL, my background is mostly in PHP. And can I not have a WHERE clause within my INSERT? I only want to change the values in the Selected column in my database for the items that have been selected from my List Box, so I thought the WHERE clause would be appropriate.

Adam · January 8, 2010

No you can't have a WHERE clause within an INSERT, what would you be checking against?

Try this:

$update = mysql_query("
    update destinations
    set selected=1
    where id={$destChose}
");

TGWSE_GY · January 8, 2010

Yea I would try an UPDATE instead of an INSERT. Insert usually creates a new record where UPDATE allows you to update the record you are working with.

TeamCIT · January 8, 2010

No you can't have a WHERE clause within an INSERT, what would you be checking against?

Try this:
$update = mysql_query("
    update destinations
    set selected=1
    where id={$destChose}
");

That worked perfectly. Thank you guys very much, all is working as I would like now. I wish I'd have known about the UPDATE technique earlier... haha

Sign In

Need help pulling data from database (MySQL and PHP)

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information