stew art Posted September 5, 2011 Share Posted September 5, 2011 I have this PHP/MySQL code: $result = mysql_query("SELECT * FROM products where Product_ID IN (475, 465, 234, 567, 845, 22)"); $x = "1"; while($row = mysql_fetch_array($result)) { echo "$x"; echo "<br />"; if ($x==1) $one = $row['Product_ID']; if ($x==2) $two = $row['Product_ID']; if ($x==3) $three = $row['Product_ID']; if ($x==4) $four = $row['Product_ID']; if ($x==5) $five = $row['Product_ID']; $x++; } echo $one; echo "<br />"; echo $two; echo "<br />"; echo $three; echo "<br />"; echo $four; echo "<br />"; echo $five; In the statement part: SELECT * FROM products where Product_ID IN (475, 465, 234, 567, 845, 22) and when I run this: while($row = mysql_fetch_array($result)) It sorts results then displays them automatically ascending which I don't want... 22 234 465 475 845 How can I get it to display them in the order I have entered them? e.g.: 475 465 234 567 845 22 Quote Link to comment Share on other sites More sharing options...
flappy_warbucks Posted September 5, 2011 Share Posted September 5, 2011 Add ... order by {column name } desc at the end of the query. Quote Link to comment Share on other sites More sharing options...
fenway Posted September 5, 2011 Share Posted September 5, 2011 Add ... order by {column name } desc at the end of the query. You didn't read the post, did you? Use ORDER BY FIELD(). Quote Link to comment Share on other sites More sharing options...
stew art Posted September 5, 2011 Author Share Posted September 5, 2011 Hey thanks for responding, but that wont achieve the desired result, it will still sort them... I suppose what I am asking is, is it possible to 'disable' sorting, and just display the results as they were requested? e.g. is this possible? SELECT * FROM products WHERE Product_ID IN (475, 465, 234, 567, 845, 22) [b]ORDER BY NONE[/b] while($row = mysql_fetch_array($result)) returns: 475 465 234 567 845 22 Quote Link to comment Share on other sites More sharing options...
stew art Posted September 5, 2011 Author Share Posted September 5, 2011 Use ORDER BY FIELD(). Thanks Fenway, I gave it a go but get error: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in (LINE X) This is how I have it: $result = mysql_query("SELECT * FROM products where Product_ID IN ($allproducts) ORDER BY FIELD()"); $x = "1"; while($row = mysql_fetch_array($result)) - error on this line. Quote Link to comment Share on other sites More sharing options...
Zane Posted September 5, 2011 Share Posted September 5, 2011 Add ... order by {column name } desc at the end of the query. You didn't read the post, did you? Use ORDER BY FIELD(). Use field() the same way you'd use IN() SELECT * FROM table WHERE id IN (6,9,3,7,2) ORDER BY FIELD (6,9,3,7,2) Quote Link to comment Share on other sites More sharing options...
stew art Posted September 5, 2011 Author Share Posted September 5, 2011 Add ... order by {column name } desc at the end of the query. You didn't read the post, did you? Use ORDER BY FIELD(). Use field() the same way you'd use IN() SELECT * FROM table WHERE id IN (6,9,3,7,2) ORDER BY FIELD (6,9,3,7,2) Thanks Zane, I gave it a go: $result = mysql_query("SELECT * FROM products where Product_ID IN (475, 465, 930) ORDER BY FIELD(475, 465, 930)"); $x = "1"; while($row = mysql_fetch_array($result)) Still returns: 465 475 930 Quote Link to comment Share on other sites More sharing options...
Zane Posted September 5, 2011 Share Posted September 5, 2011 It probably has to do with your while statement while($row = mysql_fetch_array($result)) { echo "$x"; echo " "; if ($x==1) $one = $row['Product_ID']; if ($x==2) $two = $row['Product_ID']; if ($x==3) $three = $row['Product_ID']; if ($x==4) $four = $row['Product_ID']; if ($x==5) $five = $row['Product_ID']; $x++; } echo $one; In this, you are attempting to create new variables ... relative to the $x counter value. Yet, you're only echoing $one... unless you have the others elsewhere. This in itself is just a bad idea anyway. What do you suppose to do with fifty products? Create fifty variables? Why? Try this while() loop and see if you don't change your mind. while($row = mysql_fetch_array($result)) { echo $row['Product_ID'] . " \n"; } Quote Link to comment Share on other sites More sharing options...
stew art Posted September 5, 2011 Author Share Posted September 5, 2011 In this, you are attempting to create new variables ... relative to the $x counter value. Yet, you're only echoing $one... unless you have the others elsewhere. This in itself is just a bad idea anyway. What do you suppose to do with fifty products? Create fifty variables? Why? Try this while() loop and see if you don't change your mind. while($row = mysql_fetch_array($result)) { echo $row['Product_ID'] . "<br />\n"; } Gave that a go, still returns: 465 475 930 To explain a little more, it calls several others as well e.g.: if ($x==1) {$one_productID = $row['Product_ID']; $one_price = sprintf("%.2f", $row[5]); $one_image = $path."product_image.php?img=".$row[4]; $one_title = $row[2];} if ($x==2) {$two_productID = $row['Product_ID']; $two_price = sprintf("%.2f", $row[5]); $two_image = $path."product_image.php?img=".$row[4]; $two_title = $row[2];} I am trying to generate variables for 18 products all in the one 'mysql_query'. I can't display them one after the other as I want to scatter them between unique text and images so need to assign them to variable which I can call later on in the page. I use to have a 'mysql_query' for each product though it would take 10 seconds to load the page, this way is much faster load time. Quote Link to comment Share on other sites More sharing options...
stew art Posted September 5, 2011 Author Share Posted September 5, 2011 I suppose if this was possible (I have no idea how to get it to work), I would save a lot of code, and be feasible presuming it does not take a long time to process like multiple ''mysql_query's do" GET THE RESULTS FROM the mysql_fetch_array($result) WHERE THE PRODUCT ID IS 465 echo $row['Product_ID'] echo $row['Price'] echo $row['Image'] ----------------- GET THE RESULTS FROM the mysql_fetch_array($result) WHERE THE PRODUCT ID IS 475 echo $row['Product_ID'] echo $row['Price'] echo $row['Image'] ----------------------- GET THE RESULTS FROM the mysql_fetch_array($result) WHERE THE PRODUCT ID IS 960 echo $row['Product_ID'] echo $row['Price'] echo $row['Image'] Quote Link to comment Share on other sites More sharing options...
Zane Posted September 5, 2011 Share Posted September 5, 2011 Well it looks as though you're going to get them in ascending order no matter what. I'm no SQL genius so I can't really come up with some cool cure-all query. In your situation, I would get all the results like they are, put them into a multidimensional array and use the variables accordingly. And use the product id as the index in this array. For instance. while($row = mysql_fetch_array($result)) { $rows[$row['Product_ID']] = $row; } // Now you have an array like this echo "" , print_r($rows) , ""; /// To access one of these product arrays.. simply use the Product_ID. echo $rows['465']; // ... etc Now, to print out all of the fields for each product, you will use a foreach() loop. The main problem now though is that you need to loop through the array of arrays. This is where you can take advantage of PHP and order your array the way you see fit. Simply put.. $desiredProducts = array(475,465,930); foreach($desiredProducts as $product) { foreach($rows[$product] as $field => $data) echo $field . " " . $data . " \n"; } Quote Link to comment Share on other sites More sharing options...
fenway Posted September 5, 2011 Share Posted September 5, 2011 The first argument to FIELD() should be ProductID. Quote Link to comment Share on other sites More sharing options...
Zane Posted September 5, 2011 Share Posted September 5, 2011 I'm no SQL genius so I can't really come up with some cool cure-all query. The first argument to FIELD() should be ProductID. There's your cure-all solution.. listen to fenway. Quote Link to comment Share on other sites More sharing options...
stew art Posted September 5, 2011 Author Share Posted September 5, 2011 The first argument to FIELD() should be ProductID. Thanks guys, I will give it a go this afternoon. What specificity do you mean that the first argument should be productID? e.g.? ? SELECT * FROM table WHERE id IN (6,9,3,7,2) ORDER BY FIELD (productID, 6,9,3,7,2) Quote Link to comment Share on other sites More sharing options...
stew art Posted September 6, 2011 Author Share Posted September 6, 2011 Thank you for your help fenway and Zane - it is now working. If you ever need help with CSS/HTML or SEO just send me pm any time as this is my area - will be happy to help. I'm sure I will be back when I get stuck again Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.