Need a Fresh Pair of Eyes

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
wolfcry
Forum Newbie
Posts: 8
Joined: Sun Mar 30, 2008 4:16 pm

Need a Fresh Pair of Eyes

Post 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.
AMCH
Forum Commoner
Posts: 31
Joined: Sun Mar 30, 2008 4:39 pm

Re: Need a Fresh Pair of Eyes

Post by AMCH »

It looks like your variable contains " ' " which will mess up your query.

Kind Regards
AMCH
wolfcry
Forum Newbie
Posts: 8
Joined: Sun Mar 30, 2008 4:16 pm

Re: Need a Fresh Pair of Eyes

Post by wolfcry »

Thanks AMCH

I took your suggestion and ran with it but I still received the error.
AMCH
Forum Commoner
Posts: 31
Joined: Sun Mar 30, 2008 4:39 pm

Re: Need a Fresh Pair of Eyes

Post 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
Last edited by AMCH on Sun Mar 30, 2008 7:19 pm, edited 1 time in total.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Need a Fresh Pair of Eyes

Post by Christopher »

Actually it looks like your SQL is WHERE submission_id = '' and null is probably not valid for that field.
(#10850)
AMCH
Forum Commoner
Posts: 31
Joined: Sun Mar 30, 2008 4:39 pm

Re: Need a Fresh Pair of Eyes

Post 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:
AMCH
Forum Commoner
Posts: 31
Joined: Sun Mar 30, 2008 4:39 pm

Re: Need a Fresh Pair of Eyes

Post by AMCH »

If I was you I would echo the variables out prior to your sql to make sure they are correct. 8O

:D
wolfcry
Forum Newbie
Posts: 8
Joined: Sun Mar 30, 2008 4:16 pm

Re: Need a Fresh Pair of Eyes

Post 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?
wolfcry
Forum Newbie
Posts: 8
Joined: Sun Mar 30, 2008 4:16 pm

Re: Need a Fresh Pair of Eyes

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Need a Fresh Pair of Eyes

Post 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.
wolfcry
Forum Newbie
Posts: 8
Joined: Sun Mar 30, 2008 4:16 pm

Re: Need a Fresh Pair of Eyes

Post 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.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: Need a Fresh Pair of Eyes

Post 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)
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
AMCH
Forum Commoner
Posts: 31
Joined: Sun Mar 30, 2008 4:39 pm

Re: Need a Fresh Pair of Eyes

Post 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
wolfcry
Forum Newbie
Posts: 8
Joined: Sun Mar 30, 2008 4:16 pm

Re: Need a Fresh Pair of Eyes

Post 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!
Post Reply