Problem with "Order BY"

[SamCec]

Hi: I'm new to PHP and Mysql. Wondering if you can help? This code works without the "order by" clause but with it, I get a parsing error. I do want the records sorted by "objectname", I also tried "ordered by  $item['objectName']". That didn't work either. I got the same error.

$selectObjects =    "SELECT * FROM `objecten` [color=red]ORDER BY $item['objectName']";[/color]
$queryObjects = mysql_query($selectObjects) or die(mysql_error());

while($item = mysql_fetch_array($queryObjects))
{
    array_push($objects, array(    $item['objectName'], split(";", $item['photos']), $item['price'], $item['base'], 
                                $item['centerPiece'], $item['name'], $item['wood']));
}

[kickstart]

Hi

 

You can't embed an array member in a string like that.

 

Easiest is:-

 

$selectObjects =    "SELECT * FROM `objecten` ORDER BY ".$item['objectName']." ";

 

All the best

 

Keith

[SamCec]

Thanks for the suggestion but I pasted the code you supplied in my application and got this error: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1"

 

Sam

[kickstart]

Hi

 

What is the value of $item['objectName']? It should be a column name from the query.

 

Change you or die to:-

 

$queryObjects = mysql_query($selectObjects) or die(mysql_error()." $selectObjects");

 

This should mean you can see what the SQL generated looks like.

 

All the best

 

Keith

[chintansshah]

use below as an query

 

$selectObjects =    "SELECT * FROM `objecten` ORDER BY ".$item['objectName']." ";

 

[SamCec]

chintansshah:

 

Your code gave me the following error:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1"

 

Sam

[kickstart]

Hi

 

What is the value of $item['objectName']? If it isn't a column name (or number) then your SQL will fail.

 

Put in the change I gave earlier for the or die and if should put out the generated SQL when it fails. The post that SQL up.

 

All the best

 

Keith

[SamCec]

KickStart:

 

Here is the error I received after changing the "die" clause:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 SELECT * FROM `objecten` ORDER BY

[PFMaBiSmAd]

Doesn't that output suggest to you that $item['objectName'] is empty or does not exist at all?

 

kickstart already asked what is the value of $item['objectName']? What code is setting it or where is it coming from?

[kickstart]

Hi

 

As PFMaBiSmAd says, looks like isn't set (or is set to blank). This suggests the error is a php or logic error in your code rather than an SQL error.

 

All the best

 

Keith

[SamCec]

Folks: let me re-explain: Someone else wrote this for me because I DON'T KNOW php. Here are the 1st 28 lines of the app. PLEASE SEE MY COMMENTS AFTER THIS CODE.

<?php

include('connect/connect.php');
mysql_select_db($database) or die ("Unable to Connect to the database");

$woods = array();

$selectWood =    "SELECT * FROM `wood`";
$queryWood = mysql_query($selectWood) or die(mysql_error());

while($item = mysql_fetch_array($queryWood))
{
    $woods[$item['index']] = array($item['name'], $item['photo']);
}

$objects = array();

$selectObjects =    "SELECT * FROM `objecten` ORDER BY ".$item['objectName']." ";

$queryObjects = mysql_query($selectObjects) or die(mysql_error()." $selectObjects");

while($item = mysql_fetch_array($queryObjects))
{
    array_push($objects, array(    $item['objectName'], split(";", $item['photos']), $item['price'], $item['base'], 
                                $item['centerPiece'], $item['name'], $item['wood']));
}

without the ORDER BY clause the code works fine. I know a little SQL so I originally added the "order by". When it didn't work, that's when I came for help.

 

Sam

[Pikachu2000]

The logic looks fatally flawed to me. The ORDER BY in the second query can only ever have the value of $item['objectName'] from the last record returned from the previous query. Unless your `objecten` table has a field name that corresponds to every possible value in the `objectName` field of the `woods` table, this ain't gonna work (and if it does have all of those values as field names, the database design is probably pretty scary).

 

Maybe if you post the structure of the two tables, some representative data, and a concise description of what you're trying to accomplish, someone will be able to better help you with it.

[SamCec]

It's going to take me some time to figure how to do what your asking. I am aware that "objectname" is populated. There is a record for each object. The objects are: Bunny, Butterfly, Cardinal, etc.

You can see all of the objects by going to: www.woodartbysam.com/test If you go there, please keep in mind there is still a lot of work to be done on the site.

 

Sam

[kickstart]

Hi

 

Starting from the beginning.

 

In php a variable has a name that starts with a $. You can have arrays of variables, which is what $item['objectName'] is.

 

What your current code is trying to do is to order the returned records from the SQL in the order of the VARIABLE $item['objectName']. If $item['objectName'] has a value of fred then the SQL will try and order the returned rows in the ascending order of the column called fred. If you do not have a column called fred then you will get an error.

 

I suspect that objectName is a column on your second query, and I suspect that you always want the data returned in that order. As such you probably want something like this:-

 

$selectObjects =    "SELECT * FROM `objecten` ORDER BY objectName ";

 

All the best

 

Keith

[PFMaBiSmAd]

Edit: Basically says the same as above ^^^^

 

I'm going to take a wild guess here. Do you want to add an ORDER BY to the `objecten` table because you want the records ordered by the objectName column that is in that table?

 

If so, your query would be -

 

$selectObjects =    "SELECT * FROM `objecten` ORDER BY objectName";

 

We only see the information that you supply in your posts. When you post code that contains a php variable $item['objectName'] somewhere in it, we have to assume that IS what you wanted to put into the code for some reason that you have, especially if you don't state what it is you are trying to accomplish.

 

It is always best to state what it is you are trying to accomplish, not just that something you are showing us does not work.

[SamCec]

KickStart and PFMaBiSmAd:

Sorry for the delay in getting back to you, I had to go out.

 

Thank you so much for the corrected code and the explanation. Your code did the trick. I now have the items in alphabetic order.

 

Thanks Again,

Sam