can I have two queries at the same time?

[otixz]

Hi I have two queries...

 

mysql_connect('localhost','root','');

mysql_selectdb('ereport') or die (mysql_error());

 

$qry = mysql_query("select * from tblcourses");

$row = mysql_num_rows($qry);

while($b = mysql_fetch_array($qry))

{

$course = $b['coursename'];

$initial = $b['initial'];

$title = $b['TITLE'];

 

$query = mysql_query("select total from tblinquiries where course='$course' and inq_date='4/21/2008'");

$rows = mysql_num_rows($query);

 

while($a= mysql_fetch_array($query))

{ command goes here

                        }

}

 

I need two data from this two queries. I run my program and had an error: Warning: mysql_num_rows(): supplied argument is not a valid MySQL

 

how can i solve this? please help.

 

[GingerRobot]

1.) Whenever you have a problem with a query, you need to debug it. Do something like:

 

<?php
$query = mysql_query("select total from tblinquiries where course='$course' and inq_date='4/21/2008'");  
//becomes
$sql =  "select total from tblinquiries where course='$course' and inq_date='4/21/2008'";
$query = mysql_query($sql) or die(mysql_error());
echo '<br />Query was: '.$sql;
?>

 

2.) Your approach will be slow, since you're performing a lot of queries. You'd be better of using a join/subquery to select data from two tables. If you post up what your trying to achieve (e.g. output) and your table structure, we might be able to help with that.

 

3.) When you post code, try and remember to put it inside

tags

 

[otixz]

Hi and thanks for your reply.

 

I have two tables

 

a.) tblcourse

b.) tblinquiry

 

WHat I want to do is to list all the course located at the tblcourse and at the same time see if there is any total value in the tblinquiry. what can mysql command can i use?

what I want to achieve is this kind of query:

lets say the date is: 4/21/2008

Course

Total

 

 

All course from the tblcourse will be listed hereTotal will be listed here if there is none 0 will be shown

 

 

Please help

 

What I did is I made a query first for the whole course list then I used the $course for another query.

[GingerRobot]

Sounds like you want something along the lines of:

 

 

<?php
//connect to database
$sql = "SELECT c.coursename,i.total FROM tblcourses AS c,tblinquiries AS i WHERE c.coursename=i.course and i.inq_date='4/21/2008'";
$result = mysql_query($sql) or die(mysql_error());
while(list($name,$total) = mysql_fetch_row($result)){
    echo '<br />Course: '.$name.' - total: '.$total;
}
?>

 

That's untested, but should work. One thing i see is that you're storing your dates in the format DD/MM/YYYY. This isn't the format of a mysql DATE field, so i can only assume you're using a varchar/text field. You might want to consider changing to an appropriate date type in case you need to do any future sorting.