Jump to content

Prevent automatic sorting of sql select query


stew art

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

 

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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)

 

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.