Loops on SQL Select

Question

I have a problem, my PHP page make loops.

I select into my DB only 2 informations like this :

$sqlins="SELECT hardware.name, rd.cmd
FROM hardware, rd
WHERE hardware.name LIKE 'Boomee'
AND rd.cmd LIKE 'putty'";
$toto = mysql_query($sqlins);

It's an exemple of my request. And when I use the loop While like this :

while ($data=mysql_fetch_array($toto)) {
    echo $data[1];
    echo "<br>";
}

I don't understand why my output send me many time the same line. While on my DB, I have 30 entries. I want to display them all... but the code sended me 2700!

My real script is a bit more complicate than this, I use preg_replace, implode etc, and much more "AND LIKE" in my SQL Query.

I hope you can help me. And please help me.

Your SQL query is probably returning a cross join (cartesian product) of the 2 tables. You need to join them on a mutaul field. — Adriaan Stander
– Adriaan Stander, Commented Dec 23, 2013 at 9:57

Joe Taras · Accepted Answer · 2013-12-23 10:01:12Z

3

This query:

SELECT hardware.name, rd.cmd
FROM hardware, rd
WHERE hardware.name LIKE 'Boomee'
AND rd.cmd LIKE 'putty'

perform a cartesian product.

If you don't want perform this action you must to put in relation your 2 tables hardware and rd.

I don't know if hardware is parent of rd or viceversa. But if exists a field to link two tables you add this condition in WHERE clause.

Alternatively (adviced) you rewrite your query with explicit JOIN in this way:

SELECT hardware.name, rd.cmd
FROM hardware
INNER JOIN rd
ON hardware.field = rd.field
WHERE hardware.name LIKE 'Boomee'
AND rd.cmd LIKE 'putty'

answered Dec 23, 2013 at 10:01

Joe Taras

15.4k7 gold badges45 silver badges59 bronze badges

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

Comments

user3114471 · Accepted Answer · 2013-12-23 10:47:24Z

1

Yes i have 2 field linked in hardware and rd.

The first one is "hardware.id", and the second one "rd.hardware_id".

And your method work perfectly.

I have change my query like this, because i have to learn what is INNER JOIN and ON before using it.

SELECT hardware.name, rd.cmd
FROM hardware, rd
WHERE hardware.id=rd.hardware_id
AND hardware.name LIKE 'Boomee'
AND rd.cmd LIKE 'putty'

So thanks you a lot. by the same way it's gonna help me for the pursuit of link between table.

answered Dec 23, 2013 at 10:47

user3114471

1532 silver badges11 bronze badges

Collectives™ on Stack Overflow

Loops on SQL Select

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related