MySQL - A query based on another query

Question

I write a lot of queries resembling the query example code below. I was wondering whether there was a more efficient code/script?

$query1 ="SELECT * FROM table1 WHERE date >= '$todaysdate' ";
$result1 = mysql_query($query1)
    or die ("Error in query: $query1. " . mysql_error());
if (mysql_num_rows($result1) > 0) {
    while($row1 = mysql_fetch_object($result1)) {

        echo "$row1-date";

        $query2 ="SELECT * FROM table2 WHERE table1ID >= '$row1-table1ID' ";
        $result2 = mysql_query($query2)
            or die ("Error in query: $query2. " . mysql_error());
        if (mysql_num_rows($result2) > 0) {
            while($row2 = mysql_fetch_object($result2)) {
                echo "$row->datatable2";
            }
        }
    }
}

I hope your tables aren't actually called table1 and table2; I hope you understand how to avoid SQL injection (use the search on StackOverflow if not) — MarkR
– MarkR, Commented Dec 20, 2009 at 12:19

Daniel Vassallo · Accepted Answer · 2009-12-23 10:18:51Z

9

Try using SQL JOINs, like the following example:

SELECT 
    * 
FROM 
    table1 
INNER JOIN 
    table2 ON (table2.table1ID = table1.ID)
WHERE 
    table1.date >= '2009-12-20';

edited Dec 23, 2009 at 10:18

answered Dec 20, 2009 at 12:05

Daniel Vassallo

346k72 gold badges514 silver badges447 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MatBailie Over a year ago

Agreed, SQL is far more than just "SELECT * FROM <foo> WHERE <baa>", I'd advise working through a tutorial on MySQL specifically (as opposed to a PHP tutorial's snippet of MySQL)

Daniel Vassallo · Accepted Answer · 2009-12-20 23:39:25Z

1

I don't know about the structure of your tables, but I've modified your code so that it uses a join:

$query = 'SELECT table1.date, table2.datatable2 FROM table1, table2 WHERE table1.date >= \''.$todaysdate.'\' AND table2.table1ID >= table1.table1ID';
$result = mysql_query($query)
    or exit('Error in query: '.$query.' '.mysql_error());

if (mysql_num_rows($result) > 0)
{
    while($row = mysql_fetch_object($result))
    {
        echo $row->date;
        echo $row->datatable2;
    }
}

In this case you select multiple tables with FROM separated with commas, but you can also use INNER/OUTER/LEFT/RIGHT JOIN (see the link in the first answer).

edited Dec 20, 2009 at 23:39

Daniel Vassallo

346k72 gold badges514 silver badges447 bronze badges

answered Dec 20, 2009 at 14:23

Frank

4063 silver badges9 bronze badges

Comments

Daniel Vassallo · Accepted Answer · 2009-12-21 08:59:00Z

-1

You can use PDO.
Your queries should look like this..

$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();

$result = $sth->fetchAll(PDO::FETCH_COLUMN);
// OR
$result = $sth->fetchAll(PDO::FETCH_OBJ);
var_dump($result);

PDO MANUAL: http://php.net/manual/en/book.pdo.php

edited Dec 21, 2009 at 8:59

Daniel Vassallo

346k72 gold badges514 silver badges447 bronze badges

answered Dec 20, 2009 at 12:30

Alex

11 bronze badge

1 Comment

Franz Over a year ago

Doesn't answer the question. It's a good hint, but not really what he wants in this case...

Collectives™ on Stack Overflow

MySQL - A query based on another query

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related