[RESOLVED] MySQL OR query results

sohost

Hello,

I am attempting to pull some records from a MySQL database using OR.

I basically want all records that correspond to one of three types.

For example, I want to pull the id for the records that are either example1, example2, or example3.

When I do this in PHPMyAdmin, it works just fine and I get all results I expect.

However, when I try in PHP, I only ever get the last result I expect. It is like it only gives me the end result of the last OR query.

Here is the code I am using:

$myquery = sprintf("SELECT id FROM products WHERE type = 'example1' OR type = 'example2' OR type = 'example3'");
$myresult = mysql_query($myquery);
$my = mysql_fetch_assoc($myresult);
echo '<pre>';
print_r($my);

This is the result of the above...

Array
(
[id] => 128
)

Does anyone have experience with using OR to get results to match specific types of records, based on the OR's?

I actually need to to give me the following (which is all the id results for the three types of products I am looking for):

Array
(
[id] => 128
[id] => 245
[id] => 246
)

dagon

while ($row = mysql_fetch_assoc($result)) {
...
}

look at the manual example for mysql_fetch_assoc()

sohost

I did try that, using mysql_fetch_result, but still only get a single resulting row.

while ($row = mysql_fetch_assoc($my)) {
echo $row["id"];
}

dagon

your doing it wrong:

$myquery = sprintf("SELECT `id` FROM `products` WHERE `type` = 'example1' OR `type` = 'example2' OR `type` = 'example3'");
$myresult = mysql_query($myquery);


while ($row = mysql_fetch_assoc($myresult)) {
echo $row["id"];
}

halojoy

mysql_fetch_result
is not what we normally use

either:
mysql_fetch_array
mysql_fetch_assoc
... is what to use

$myquery = sprintf("SELECT `id` FROM `products` WHERE `type` = 'example1' OR `type` = 'example2' OR `type` = 'example3'");
$myresult = mysql_query($myquery);

echo '<pre>';
// take row by row in loop from the result
while ($row = mysql_fetch_assoc($myresult)) {
    print_r($row);
}

sohost

Thanks guys, my bad. I should read and practice more before replying 🙂

Thanks for the help again. After reading your posts and re-reading the manual again, I eventually got the results I expected.

I appreciate all the help guys, I really do.

sohost

I have another question, related to the same initial question...

I want to now take the three results i got and go and find items in the database that match these products, if you know what i mean.

As it stands, i am getting the results in 3 arrays, 1 each.

Array
(
[id] => 128
)

Array
(
[id] => 245
)

Array
(
[id] => 246
)

How would i now be able to loop through each result, in each array and create a single new array containing the results of all matches for all three types?

So, if i have this:

$myquery = sprintf("SELECT id FROM products WHERE type = 'example1' OR type = 'example2' OR type = 'example3'");
$myresult = mysql_query($myquery);

echo '<pre>';
// take row by row in loop from the result
while ($row = mysql_fetch_assoc($myresult)) {
print_r($row);
}

Must i have a new while or new foreach inside the existing while?

I'm a little confused here, as usual 🙂

I basically need to first find all products that match a specific type. then i need to go and match actual customers products that match the product types.

bradgrafelman

You could probably combine the two steps into a single query. Can you give us more information about what you mean by "match actual customers products" ?

dagon

so you actully want a join and you only need one querry, something like this (i dont know your table or field names so you will have to adapt it)

SELECT * from customers_products
LEFT JOIN products on (products.id=customers_products.type)
WHERE type in ('example1','example2','example3')

I used in in the where clause, same as your or's

sohost

Okay, here is the basic idea of what i need to achieve.

$myquery = sprintf("SELECT id FROM products WHERE type = 'example1' OR type = 'example2' OR type = 'example3'");
$myresult = mysql_query($myquery);

echo '<pre>';
// take row by row in loop from the result
while ($row = mysql_fetch_assoc($myresult)) {
print_r($row);
}

Which gives me this:

Array
(
[id] => 128
)

Array
(
[id] => 245
)

Array
(
[id] => 246
)

Now, i need to take each one of the above results and go and find the actual customer products that match any of the above.

In PHPMyAdmin, I use this and it does work and gives me all products that match, but i statically specify the above in PHPMyAdmin.

SELECT id, userid, host FROM customerproducts WHERE productid = "128" AND status = "Active" OR productid = "245" AND status = "Active" OR productid = "246" AND status = "Active";

I guess i'm just not sure where to put the above (in the while or create a new while or foreach).

bradgrafelman

dagon's example query shows how you can use a LEFT JOIN to do the two steps in one single query.

Rather than doing the SELECT to find the id's, you'd simply use dagon's example query instead.

sohost

Wow, that is awesome guys. I have not played with left join's, but now that you have given me an example I can relate to, I am going to spend a couple of hours learning about the various types of joins. Thanks again, you guys are amazing and I appreciate all the help and tips.