Page 1 of 1

Need a Fresh Pair of Eyes

Posted: Sun Mar 30, 2008 5:36 pm
by wolfcry
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 65. I cannot get the data to actually insert into the database and I keep receiving this error:

Code: Select all

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
For the life of me cant see the error.

Code: Select all

 
    <form action="<?php $_SERVER[PHP_SELF];?>" method="post">
    &nbsp;&nbsp;City: <input type="text" name="city">&nbsp;&nbsp;State / Province: <input type="text" name="state">&nbsp;&nbsp;
    <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 "&nbsp;&nbsp;$i&nbsp;&nbsp;"; 
    } else { print "&nbsp;&nbsp;<a href='search.php?
                       city=$city_location&state=$state&page_number=$i'>$i</a>&nbsp;&nbsp;"; } 
 
 
}
 
// 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 &nbsp;$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>";
 
  
}
 
?>
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.

Re: Need a Fresh Pair of Eyes

Posted: Sun Mar 30, 2008 5:45 pm
by AMCH
It looks like your variable contains " ' " which will mess up your query.

Kind Regards
AMCH

Re: Need a Fresh Pair of Eyes

Posted: Sun Mar 30, 2008 7:01 pm
by wolfcry
Thanks AMCH

I took your suggestion and ran with it but I still received the error.

Re: Need a Fresh Pair of Eyes

Posted: Sun Mar 30, 2008 7:09 pm
by AMCH
Ah yes upon closer inspection I see the problem. You have used "INSERT" instead of "UPDATE".

Insert should be used to insert a new line and update is best for altering a line as far as I am aware.

Hope this helps

Kind Regards
AMCH :D

Re: Need a Fresh Pair of Eyes

Posted: Sun Mar 30, 2008 7:15 pm
by Christopher
Actually it looks like your SQL is WHERE submission_id = '' and null is probably not valid for that field.

Re: Need a Fresh Pair of Eyes

Posted: Sun Mar 30, 2008 7:18 pm
by AMCH
arborint wrote:Actually it looks like your SQL is WHERE submission_id = '' and null is probably not valid for that field.
Now that I never thought about! :crazy:

Re: Need a Fresh Pair of Eyes

Posted: Sun Mar 30, 2008 7:21 pm
by AMCH
If I was you I would echo the variables out prior to your sql to make sure they are correct. 8O

:D

Re: Need a Fresh Pair of Eyes

Posted: Sun Mar 30, 2008 8:29 pm
by wolfcry
I really appreciate all the help.

Ok, atleast I now know that my problem lies with the WHERE condition. If I remove it and use either UPDATE or INSERT without a WHERE condition I do not receive the error. I just cannot find that darn error :banghead:

I know I need the WHERE condition inorder to match the correct IDs.

As far as the echoing is concerned, everything echos correctly so there is values being stored.

Any thoughts?

Re: Need a Fresh Pair of Eyes

Posted: Sun Mar 30, 2008 9:17 pm
by wolfcry
Alright, I finally got the errors to go away but it's still eating at me by not working properly.

I am now using this:

Code: Select all

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'";
which works, atleast I am told it's working by the default success message. If I change everything to INSERT (which I know only adds a new row, then I must remove the WHERE condition otherwise it'll spit errors again, but it will insert a new row.)

As for the $id part (which I recently renamed) arborint was right. It is null. I hard coded a integer depicting an actual submission id and that form updated as it should have.

What am I doing wrong? This shouldnt be this difficult :?

I've tried making the $row[submission_id] into a global variable, performing a new sql select query before the update query and a few others just to experiment but nothing is working.

Re: Need a Fresh Pair of Eyes

Posted: Sun Mar 30, 2008 11:09 pm
by John Cartwright
Firstly, always quote your array indices.

$row[submission_id] vs. $row['submission_id']

Try adding error_reporting(E_ALL); at the top of your script to see if any variables are undefined. Secondly, I would suggest adding var_dump()'s or echo's throughout your script tracing the value for $sub_id. I notice the value comes from a query, so try var_dump()'ing the contents of the query to see if the value is present there. Bascally, work your way backwards.

Echo'ing out the query also helps.

Re: Need a Fresh Pair of Eyes

Posted: Mon Mar 31, 2008 5:46 pm
by wolfcry
Hi Jcart,
I did that and was told pretty much what I already knew and that is the variable $sub_id is empty (the query is empty).

I've tried to fix this by doing many things all which didn't work. How do I pass the data stored in $row['submission_id'] outside that original while() loop?

I know I'm missing something I just cant remember or see what.

Thanks alot for all of your help. It is very much appreciated.

Re: Need a Fresh Pair of Eyes

Posted: Mon Mar 31, 2008 5:53 pm
by s.dot
Capture the id's inside of the while loop.

Code: Select all

$captured = array();
 
while (...)
{
   $captured[] = $row['submitted_id'];
}
 
//put captured into a comma delimited list
$captured = implode(',', $captured);
 
//update
.... WHERE `submisstion_id` IN($captured)

Re: Need a Fresh Pair of Eyes

Posted: Mon Mar 31, 2008 5:55 pm
by AMCH
wolfcry wrote:Hi Jcart,
I did that and was told pretty much what I already knew and that is the variable $sub_id is empty (the query is empty).

I've tried to fix this by doing many things all which didn't work. How do I pass the data stored in $row['submission_id'] outside that original while() loop?

I know I'm missing something I just cant remember or see what.

Thanks alot for all of your help. It is very much appreciated.

Code: Select all

while (blah)
{
global $submission_id;
}
This will allow you to use the variable after the while loop has closed.

AMCH :D

Re: Need a Fresh Pair of Eyes

Posted: Mon Mar 31, 2008 11:38 pm
by wolfcry
Hey all.

I wanted to give you all a great big thank you for taking the time to help me debug my code.

I found the error, as in why the variable $sub_id wasn't being called into the sql query.

I forgot to capture that value within the form I was trying to use to submit the information lol. Wowza, 2 1/2 days to see a problem I should've seen rather quickly. O well, tis the life of programming I guess lol. The query kept coming up empty because there was no value after the form was submitted.

Again, thank you all so much!