Hi all and its great being here. I need a fresh pair of eyes to go over some code (a really simple search script at the moment) and help me locate where it's not working properly. Everything works as it should meaning, everything prints out of the database as its supposed to, the functions work, all pagination works etc. The problem I have is the INSERT section and it can be found starting on line 61. I cannot get the data to actually insert into the database and I keep receiving this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE submission_id = ''' at line 1 Code (markup): and for the life of me cant see the error. <form action="<?php $_SERVER[PHP_SELF];?>" method="post"> City: <input type="text" name="city"> State / Province: <input type="text" name="state"> <input type="submit" name="search" class="button" value="Search"> </form> <?php $form_id = 1; // now retrieve and clean all submissions in this location function clean($str) { $str = @trim($str); if(get_magic_quotes_gpc()) { $str = stripslashes($str); } return mysql_real_escape_string($str); } // Place cleaned variables here $city_location = clean($_REQUEST['city']); // Sanitize more throughly $state = clean($_REQUEST['state']); // Sanitize more throughly // Perform Query For Pagination $result = mysql_query("SELECT COUNT(*) AS total_entries FROM $form_id WHERE city = '$city_location' AND state ='$state'") or die("Uh Oh" . mysql_error()); $row = mysql_fetch_row($result); $total_entries = $row[0]; $entries_per_page = 5; if(isset($_GET['page_number'])) { $page_number = $_GET['page_number']; } else { $page_number = 1; } $total_pages = ceil($total_entries / $entries_per_page); $offset = ($page_number - 1) * $entries_per_page; // Perform query for printing data in rows $result = mysql_query("SELECT * FROM $form_id WHERE city = '$city_location' AND state = '$state' LIMIT $offset, $entries_per_page") or die(mysql_error()); while ($row = mysql_fetch_array($result)) { echo "<table>"; echo "<tr><td><b>Date of Submission:</b> {$row['submission_date']}</td></tr>"; echo "<tr><td><b>Location Name:</b> {$row['col_2']}</td></tr>"; echo "<tr><td><b>City / Town:</b> {$row['col_3']}</td></tr>"; echo "<tr><td><b>State / Province:</b> {$row['col_4']}</td></tr>"; echo "<tr><td><b>GPS Data:</b> {$row['col_5']}</td></tr>"; echo "<tr><td><b>Description:</b> {$row['col_6']}</td></tr>"; echo "</table>"; // If data field is blank, allow user to submit information. if ($row['col_5'] == "") { echo "<form action='$_SERVER[PHP_SELF]' method='post'>"; echo "<input type='text' name='gps'>"; echo "<input type='submit' name='update' class='button' value='Update GPS'>"; echo "</form>"; } echo "<hr size=\"1\" color=\"green\"/>"; // just for display purposes to separate the entries } // Insert user submitted info into database. if (isset($_POST['update'])) { $new_gps = clean($_POST['gps']); $form_id = 24; $id = $row['submission_id']; $sql = "INSERT INTO ft_form_{$form_id} SET col_5 = '$new_gps' WHERE submission_id = '$id'"; if(@mysql_query($sql)) { echo "<p>The GPS data has been updated. </p>"; } else { echo '<p>GPS update not successful.</p>' . mysql_error() . '</p>'; } } for($i = 1; $i <= $total_pages; $i++) { if($i == $page_number) { print " $i "; } else { print " <a href='search.php? city=$city_location&state=$state&page_number=$i'>$i</a> "; } } // User MUST search by city AND state otherwise an error message will display telling them if (isset($_POST['search']) AND !$state) { echo "<p>Please input state / province into search box.</p>"; } // If search was empty allow user to submit normal form post to that location. Auto fill city and state fields elseif (isset($_POST['search']) AND mysql_num_rows($result) == 0) { echo "<p>Sorry, there have been no submissions in this location.</p>"; echo "<div>"; echo "<h1>Area Submission Form For $city_location $state</h1>"; echo "<p>Be the first to submit information for this area!</p>"; echo "<p>"; echo "<form action='myprocess.php' class='searchform' method='post'>"; echo "<p>"; echo "Location Name: <input type='text' name='loc_name' class='textbox' /><br />"; echo "City / Town: <input type='text' name='city' class='textbox' value='$city_location' /><br />"; echo "state / Province: <input type='text' name='state_prov' class='textbox' value='$state' /><br />"; echo "GPS Data: <input type='text' name='gps' class='textbox' /><br />"; echo "Description: <textarea class='textarea1' name='description' rows='5' cols='5'></textarea><br />"; echo "<input name='submit' class='button' value='Submit' type='submit' />"; echo "</p>"; echo "</form>"; echo "</div>"; } ?> PHP: I understand I did things alittle unorthodox above, but this is a simple search script included in a very complex program I'm working on and threw it together to get the ball rolling and will "reconstruct" later. I just really need to figure out why I cannot get the INSERT to work. Thanks for all of your help in advance.
It seems $id = $row['submission_id']; isn't working. Are you sure $row is correct and it's not meant to be $_POST or something? After the insert line add echo $sql; to see the SQL query, then find out what the problem in the query is.
Not to sure actually. I tested $_POST, $_GET and even $_REQUEST but I still received that error. However, I have $row[submission_id] being printed out above and it works so I was assuming I should be able to place that value into a variable.
Ok, I now know it is my WHERE condition that is messed up. Everything else works fine and prints the values stored when using echo. I just cannot locate that error or figure out why it has errors.
Where are you getting the value $id = $row['submission_id']; from? I don't see any other instance of submission_id.
Hi possibility. I'm getting it from here: // Perform query for printing data in rows $result = mysql_query("SELECT * FROM ft_form_{$form_id} WHERE col_3 = '$city_location' AND col_4 = '$state' LIMIT $offset, $entries_per_page") or die(mysql_error()); while ($row = mysql_fetch_array($result)) { echo "<table>"; echo "<tr><td><b>Submission ID:</b> {$row['submission_id']}</td></tr>"; echo "<tr><td><b>Date of Submission:</b> {$row['submission_date']}</td></tr>"; echo "<tr><td><b>Location Name:</b> {$row['col_2']}</td></tr>"; echo "<tr><td><b>City / Town:</b> {$row['col_3']}</td></tr>"; echo "<tr><td><b>State / Province:</b> {$row['col_4']}</td></tr>"; echo "<tr><td><b>GPS Data:</b> {$row['col_5']}</td></tr>"; echo "<tr><td><b>Description:</b> {$row['col_6']}</td></tr>"; echo "</table>"; PHP: I figured it should transfer over. Also, I got the query to work, it's saying the default success message after attempting to update, but I do not see anything so I know it has to be the id because that id variable is empty. Empty within the actual query, no where else. Here is my new query string to actually update: if (isset($_POST[update])) { $new_gps = clean($_REQUEST[gps]); $form_id = 24; $sub_id = $row[submission_id]; $sql = "UPDATE ft_form_{$form_id} SET col_5 = $new_gps, submission_date = CURDATE() WHERE submission_id = '$sub_id'"; PHP: If I'm not going about grabbing that particular submission's id right, how do I go about doing it? I've tried a thousands things except for the one right thing.
I'm not exactly sure what you mean because it is there. Its directly below the first echo "<table>"; like so: while ($row = mysql_fetch_array($result)) { echo "<table>"; echo "<tr><td><b>Submission ID:</b> {$row['submission_id']}</td></tr>"; PHP: If I'm missing something, as in if this part is not what you are talking about, then please specify what part you are talking about because I do see it. Thank you
echo "<table>"; echo "<tr><td><b>Date of Submission:</b> {$row['submission_date']}</td></tr>" Code (markup): That is submission date, not submission id.
Hey CMS, Thats weird, on my end I actually see the $row['submission_id'] just above the submission_date in the code I posted. Wonder why its not showing up for you. In either case, I do have $row['submission_id'] in my code as well. I think its a loop problem. I'm just not to sure how to solve this.
That's pretty much impossible, it's just a forum outputting plain text. Maybe you're not reading the first post in the thread.
Look it does not matter. Stop getting all hung up on one post, you'll do nothing but stress yourself out and it doesnt really look good that you keep saying it's not in this thread when it really is there. I realize it is not in post #1 but if you actually took the time to read the other posts, you'd see that in post #6, yep, I actually updated that information after possibility replied, you will see that string. Right above submission_date. Either way, if all you're trying to do is make me look like a fool, which you're not, because as mentioned earlier, that string really is there, or start a fight for no reason, go post somewhere else. Truthfully, you're not even 'attempting' to help so why bother waste your time and my time posting? I came here looking for some help and advice not your attitude. Thanks for understanding
I forgot to call the variable in the form being used. There's another form I have set up to appear if the col_3 field is empty and I have the if(isset['update'] setup with the query to update inside the function once of course the user clicks update / submit (I thought I posted the form in this thread but didn't and that was my fault). I needed to actually pass the value from the form itself because nothing was being passed for the variable to store. Also, if I was reading into the above with the attitude thing, I'm really sorry.