Prevent automatic sorting of sql select query

[stew art]

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

[flappy_warbucks]

Add

... order by {column name } desc 

at the end of the query.

[fenway]

Add

... order by {column name } desc 

at the end of the query.

You didn't read the post, did you?

 

Use ORDER BY FIELD().

[stew art]

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

[stew art]

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.

 

 

[Zane]

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)

[stew art]

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

[Zane]

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";
  }

[stew art]

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.

[stew art]

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']

[Zane]

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";
}

[fenway]

The first argument to FIELD() should be ProductID.

[Zane]

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.

[stew art]

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)

 

 

[stew art]

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