NYCPHP Meetup

[nycphp-talk] Is there something wrong with this SQL query in PHP?

Anthony Wlodarski aw at sap8.com
Tue Aug 14 15:56:55 EDT 2007


Ran the query from the command line this was the output:

 

mysql> SELECT * FROM `jobsdb`.`jobsdb` WHERE `id` =1;

+----+-----------+---------+----------+---------+----------------+--------+-
--------------+---------+--------+----------+---------+

| id | recruiter | jobid   | position | link    | additionalinfo | salary |
hiringmanager | company | status | postings | color   |

+----+-----------+---------+----------+---------+----------------+--------+-
--------------+---------+--------+----------+---------+

|  1 | lskdfj    | lsdkjfl | lskdjf   | slkdjfl | sldkfjsl       | lskdjf |
sldkfj        | dlskfj  | lskdjf | sldkfj   | #ff0000 |

+----+-----------+---------+----------+---------+----------------+--------+-
--------------+---------+--------+----------+---------+

1 row in set (0.00 sec)

 

It is correct since I only put in garbage data to test it.  And here is the
results of the script when I echoed the $query variable:

 

SELECT * FROM `jobsdb`.`jobsdb` WHERE `id` =1;
Resource id #3
Debug data
id

 

So I will definitely in the future keep an out for direct $_POST variables
directly in a SQL query (I will just save a local copy from now on and use
that.).

 

Anthony Wlodarski

Senior Technical Recruiter

Shulman Fleming & Partners

646-285-0500 x230

aw at sap8.com

 

From: talk-bounces at lists.nyphp.org [mailto:talk-bounces at lists.nyphp.org] On
Behalf Of Anthony Wlodarski
Sent: Tuesday, August 14, 2007 3:44 PM
To: 'NYPHP Talk'
Subject: RE: [nycphp-talk] Is there something wrong with this SQL query in
PHP?

 

It does evaluate to true every time, as I wouldn't get to the debug echo
statement in the application.  I put it in its own script and added a few
lines, here is the test script:

 

<?php

// our global database handle

// Always get a database handle

$dbh=mysql_connect ("localhost", "root", "plasticpen5") or die ('I cannot
connect to the database because: ' . mysql_error());

mysql_select_db ("jobsdb");

 

$query = 'SELECT * FROM `jobsdb` WHERE `id` =1;';

$result = mysql_query($query) or die("Error editing data.");

$row = mysql_fetch_assoc($result, MYSQL_ASSOC);

 

echo $result."</br>";

echo "Debug data</br>";

echo "id ".$row['id']."";

 

// close our database handle

mysql_close($dbh);

?>

I gave it the id of 1 because I know it does exist in the database, this is
the output:

 

Resource id #3
Debug data
id

 

Nothing in $row and since I have never echoed a raw result ($result) from
the query can anyone explain what it might possibly be?

 

Anthony

 

 

 

From: talk-bounces at lists.nyphp.org [mailto:talk-bounces at lists.nyphp.org] On
Behalf Of Brian O'Connor
Sent: Tuesday, August 14, 2007 3:26 PM
To: NYPHP Talk
Subject: Re: [nycphp-talk] Is there something wrong with this SQL query in
PHP?

 

Hmm, have you tried outputting the results of $_POST['editjob']?  It might
not be set to something that evaluates to true, which would cause none of
the information you're looking for to be processed.

On 8/14/07, Anthony Wlodarski <aw at sap8.com> wrote:

I have been staring at this for about a week and it is turning into a mind
bender.  Here is the code excerpt:

 

// pre: none

// post: displays our controls for .

function displayControls()

{

        if($_POST['editjob'])

        {

                $query = "SELECT * FROM `jobsdb` WHERE `id`
=".$_POST['id']."";

                $result = mysql_query($query) or die("Error editing data.");

                $row = mysql_fetch_assoc($result, MYSQL_ASSOC);

 

                echo "Debug: ".$_POST['id']." - ".$row['id']."\n";

 

                // display all our form data

                echo "<fieldset style='color: #ffffff;'><legend>Edit a
job</legend>\n".

                                "<form action='".$_SERVER['PHP_SELF']."'
method='post'>\n".

                                "Recruiter: <input name='recruiter'
type='text' value='".$row['recruiter']."'></br>".

                                " Job ID: <input name='jobid' type='text'
value='".$row['jobid']."'></br>".

                                " Position: <input name='position'
type='text' value='".$row['position']."'></br>".

                                " Position Link: <input name='link'
type='text' value='".$row['link']."'></br>".

                                " Additional Info: <input
name='additionalinfo' type='text' value='".$row['additionalinfo']."'></br>".

                                " Salary: <input name='salary' type='text'
value='".$row['salary']."'></br>".

                                " Hiring Manager: <input
name='hiringmanager' type='text' value='".$row['hiringmanager']."''></br>".

                                " Company: <input name='company' type='text'
value='".$row['company']."'></br>".

                                " Status: <input name='status' type='text'
value='".$row['status']."'></br>".

                                " Postings: <input name='postings'
type='text' value='".$row['postings']."'></br>".

                                " Job Color: \n".

                                "<select name='color'>\n".

                                "<option value='#ff0000'>Red</option>\n".

                                "<option value='#ffa500'>Orange</option>\n".

                                "<option value='#ffff00'>Yellow</option>\n".

                                "<option value='#00ff7f'>Spring
Green</option>\n".

                                "<option value='#32cd32'>Lime
Green</option>\n".

                                "<option value='#c0c0c0'>Silver</option>\n".

                                "<option value='#0000cd'>Medium
Blue</option>\n".

                                "<option value='#191970'>Midnight
Blue</option>\n".

                                "<option value='#4169e1'>Royal
Blue</option>\n".

                                "<option value='#c71585'>Medium Violet
Red</option>\n".

                                "<option
value='#ff00ff'>Fuchsia</option>\n".

                                "</select>\n".

                                "<input name='saveedit' type='submit'
value='Save Edit'>".

                                "<input name='cancel' type='submit'
value='Cancel'>".

                                "</form>".

                        "</fieldset>\n";

        }

 

... Rest of application.

 

Now the big problem are the three PHP statements after the "if" statement.
When I click edit job nothing appears even in the debug statement, I am not
returning a valid id from $row['id'] nor does any of the data appear in the
form(which is just an integer).  I have scrutinized the SQL query a million
times, even tried to qualify it with the database name using
`foobar`.`jobsdb` and that still not do anything.  I know the $_POST['id']
variable is passing the right id based on the echo command.  Is this a SQL
issue or a PHP issue?  I have looked at my databases in phpMyAdmin and even
checked them using the command line on our box and they have the right
permissions for the users that can access them.  If this is  a SQL issue can
you guys point me in the right direction?  I was thinking of pulling my data
base handle code and the SQL queries and just throwing them into a bare
script to see what happens.  If anyone needs to see the HTML output I can
get that as well.

 

Any help is appreciated.

 

Thanks,

 

Anthony


_______________________________________________
New York PHP Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk

NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com  <http://www.nyphpcon.com> 

Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php 




-- 
Brian O'Connor 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.nyphp.org/pipermail/talk/attachments/20070814/e97176f4/attachment.html>


More information about the talk mailing list