Multiple queries in one script

[Bob Sanderson]

I am relatively new to PHP and MySQL. This is the first time I've tried
to use multiple queries in a single script.

I have the following PHP script which gets a Job Number from a search
form and generates a web page which displays the record for that job:

$username="root";
$password="";
$database="foobar";

$Searchterm = $_GET['JobNumber'];

mysql_connect('localhost',$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query="select * from jobs where JobNumber like $Searchterm";

$result=mysql_query($query);
$num_results=mysql_num_rows($result);
$JobNumber=mysql_result($result,$i,"JobNumber");
$NSN=mysql_result($result,$i,"NSN");

echo various fields here

This part works fine.

----------------------------------

I now want to list other jobs which use the same NSN on the same page
under that display. This is the script I'm using:

$query2="select * from jobs where NSN like $NSN";
$result=mysql_query($query2);
$num_results=mysql_num_rows($result);
$JobNumber=mysql_result($result,$i,"JobNumber");

echo query2; (returns the correct query, including the NSN)
echo $num_results (returns nothing)

$i=0;
while ($i < $num) {

echo various fields here (returns nothing)

$i++;
}

Can anyone tell me what I'm missing?

[Erwin Moller]

Bob Sanderson wrote:

Hi Bob,

I am relatively new to PHP and MySQL. This is the first time I've tried
to use multiple queries in a single script.

I have the following PHP script which gets a Job Number from a search
form and generates a web page which displays the record for that job:

$username="root";
$password="";
$database="foobar";

$Searchterm = $_GET['JobNumber'];

mysql_connect('localhost',$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

Ok so far.
$query="select * from jobs where JobNumber like $Searchterm";
This is very dangerous.
NEVER EVER thrust input originating from a form that is filled in by some
user.
You are wide open to the SQL-Injection attack this way.

If you have magic_quotes on, you are a lot safer, but please be sure what
you are doing...

$result=mysql_query($query);
$num_results=mysql_num_rows($result);
$JobNumber=mysql_result($result,$i,"JobNumber");
$NSN=mysql_result($result,$i,"NSN");
What is $i here?
$i defines the row to be retrieved, but you didn't give it any value.

echo various fields here

This part works fine.
good. :-)

Suprisingly because you didn't define $i.....

----------------------------------

I now want to list other jobs which use the same NSN on the same page
under that display. This is the script I'm using:

$query2="select * from jobs where NSN like $NSN";
$result=mysql_query($query2);
$num_results=mysql_num_rows($result);
$JobNumber=mysql_result($result,$i,"JobNumber");
What is $i here?

echo query2; (returns the correct query, including the NSN)
That should be:
echo $query2;

You forgot the $
echo $num_results (returns nothing)
should return something if you fix the previous code. :-)

$i=0;
while ($i < $num) {
What is $num?
Do you mean $num_results???

echo various fields here (returns nothing)

$i++;
}

Can anyone tell me what I'm missing?

Fix the various mistakes. :-)
Good luck!

Regards,
Erwin Moller

[Bob Sanderson]

Sorry, there's a couple of typos there (in the message, not in the script).
Here's what the actual script reads, not including the for-next loop:

$query2="select * from jobs where NSN like $NSN";
$result=mysql_query($query2);
$num_results=mysql_num_rows($result);

$NSN=mysql_result($result,$i,"NSN");

echo "query = $query2<br>";
echo "num_results = $num_results<br>";

Again, the query is exactly what I want, but $num_results returns nothing
(I'm sure that there are more than 1 records meeting the criteria).

< What is $i here?

As I said, I'm new to MySql. I took an example from a book and the $i was
included. It appears that, in this case, it's unnecessary but it doesn't
seem to matter if it's there or not.

[Bob Sanderson]

Sorry, there's a couple of typos there (in the message, not in the script).
Here's what the actual script reads, not including the for-next loop:

$query2="select * from jobs where NSN like $NSN";
$result=mysql_query($query2);
$num_results=mysql_num_rows($result);

$NSN=mysql_result($result,$i,"NSN");

echo "query = $query2<br>";
echo "num_results = $num_results<br>";

Again, the query is exactly what I want, but $num_results returns nothing
(I'm sure that there are more than 1 records meeting the criteria).

< What is $i here?

As I said, I'm new to MySql. I took an example from a book and the $i was
included. It appears that, in this case, it's unnecessary but it doesn't
seem to matter if it's there or not.

[Bob Sanderson]

Sorry, there's a couple of typos there (in the message, not in the script).
Here's what the actual script reads, not including the for-next loop:

$query2="select * from jobs where NSN like $NSN";
$result=mysql_query($query2);
$num_results=mysql_num_rows($result);

$NSN=mysql_result($result,$i,"NSN");

echo "query = $query2<br>";
echo "num_results = $num_results<br>";

Again, the query is exactly what I want, but $num_results returns nothing
(I'm sure that there are more than 1 records meeting the criteria).

< What is $i here?

As I said, I'm new to MySql. I took an example from a book and the $i was
included. It appears that, in this case, it's unnecessary but it doesn't
seem to matter if it's there or not.

[Bob Sanderson]

Forgive me if this is posted more than once, but it is not showing up in my
news reader.

===============

Sorry, there's a couple of typos there (in the message, not in the script).
Here's what the actual script reads, not including the for-next loop:

$query2="select * from jobs where NSN like $NSN";
$result=mysql_query($query2);
$num_results=mysql_num_rows($result);

$NSN=mysql_result($result,$i,"NSN");

echo "query = $query2<br>";
echo "num_results = $num_results<br>";

Again, the query is exactly what I want, but $num_results returns nothing
(I'm sure that there are more than 1 records meeting the criteria).

< What is $i here?

As I said, I'm new to MySql. I took an example from a book and the $i was
included. It appears that, in this case, it's unnecessary but it doesn't
seem to matter if it's there or not.

[deja]

Hi Bob,

What you are missing are quotes. Your second query should read:

$query2="select * from jobs where NSN like '$NSN' ";

My recommendation is to print the query out when you debugging your
code - and then do a copy/paste into something like phpMyAdmin or Mysql
Command Center or at the Mysql command line - basically test the query
out in an app that will let you make quick changes and retest. When
you are able to get the query working correctly, then copy it back into
your code and (replacing vars as needed).

-CF

[deja]

Hi Bob,

If $i is not perviously set, then it will equal "". It should equal an
integer value of 0 or greater. This probably belongs on the PHP
newgroup, but the PHP/Mysql community tends to be pretty tight.

mysql_result is a function that is defined as:
mysql_result ( resource result, int row [, mixed field] )

A VERY handy resource (one of the best programmer API documentation
emplemetation I have come across) is PHP.net. Specificially see this
link:

http://us3.php.net/manual/en/function.mysql-result.php

-CF

[Bob Sanderson]

Sorry, there's a couple of typos there (in the message, not in the script).
Here's what the actual script reads, not including the for-next loop:

$query2="select * from jobs where NSN like $NSN";
$result=mysql_query($query2);
$num_results=mysql_num_rows($result);

$NSN=mysql_result($result,$i,"NSN");

echo "query = $query2<br>";
echo "num_results = $num_results<br>";

Again, the query is exactly what I want, but $num_results returns nothing
(I'm sure that there are more than 1 records meeting the criteria).

< What is $i here?

As I said, I'm new to MySql. I took an example from a book and the $i was
included. It appears that, in this case, it's unnecessary but it doesn't
seem to matter if it's there or not.