mysql and php -- SQL: SELECT error

I'm having a problem and hope someone can help me. I have a page that keeps throwing the error below. It is a page that autopopulates a products "main" page which then has links to the various categories.

I finally set everything up on a different server entirely than my DH one and all works perfectly well on it, but on DH, the following error occurs.

I'm running apache as a module and not as cgi. (This is the same on both servers.)

Is there any hope of getting this to work on DH, or should I bite it and take this site elsewhere?


« Execution of a query to the database failed »
SQL: SELECT `061rc3`.etomite_product_log.prd_cat_id, `061rc3`.etomite_product_log.prd_id as item, `061rc3`.etomite_product_log.prd_name as call, `061rc3`.etomite_product_category.cat_name as name, `061rc3`.etomite_product_category.cat_page as page, max(`061rc3`.etomite_product_log.prd_dts) WHERE `061rc3`.etomite_product_log.prd_rem is null GROUP BY `061rc3`.etomite_product_log.prd_cat_id ORDER BY `061rc3`.etomite_product_category.cat_name ASC ;

and this is the code:

$pageid = $etomite->documentIdentifier;
$count = 0;

$output = '<table cellspacing=0 border=0 align=center width=90%><tr valign=top>'; // open table

# select latest entry based on settings above
$DbPre = $this->dbConfig['dbase'].".".$this->dbConfig['table_prefix'];
$t1 = $DbPre."product_log";
$t2 = $DbPre."product_category";
$sql = "SELECT $t1.prd_cat_id, $t1.prd_id as item, $t1.prd_name as call,
$t2.cat_name as name, $t2.cat_page as page, max($t1.prd_dts)
WHERE $t1.prd_rem is null GROUP BY $t1.prd_cat_id
ORDER BY $t2.cat_$sortBy $sortHow ;";
$result = $this->dbQuery($sql);
$rs = array();
for($i=0;$i<@$this->recordCount($result);$i++) {
array_push($rs,@$this->fetchRow($result));
}
# Display the existing entries in table format
if(count($rs) > 0) {
foreach($rs as $row) {
$count++;

$output .= "<td width=33%><a href=[~".$row['page']."~]>".$row['name'];
$output .= "<br/><img src=".$path_to_gal.$tmb_pre.$row['item'].".jpg alt="".$row['item'].": ".$row['call'].""></a></td>";
if ($count % $perrow == 0)
{ $output .= "</tr><tr valign=top>"; }
}
$left = count($rs) % $perrow;
if ($left == 0) {
for ($i = 1 ; $i <= $perrow ; $i++) {
$output .= "<td></td>"; }
} else {
for ($i = 0 ; $i <= $left ; $i++) {
$output .= "<td></td>"; }
}
$output .= "</tr>";
}
else
{ $output .= "There are currently no products in the set you requested.
Please try different filter options.";
}

$output .= "</table>";

return $output;

Hi

Your query is missing a 'FROM' clause to start with, so you aren't stating what table you are selecting from

your are renaming a column `061rc3`.etomite_product_log.prd_name as 'call' which is a reservered word in Mysql

http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

Also your table prefix is very odd with the `061rc3`.xx.col
I've personally never seen that before, but I can't comment on if it is right or not.

I don't think dreamhost is the issue, you have some other stuff to sort out first.

Best of luck!

Thank you for replying. I really appreciate it.

I've pasted the top of the code below with the FROM clause in it (sorry about that). I'll also look further into the other things you mentioned. It does work on another server, though and I'm wondering if it's the "join" in the FROM clause and if DH has some issue re that. I've searched the forum and the new wiki, but haven't found anything re it at all.

Do you know if "joins" are permitted?

# select latest entry based on settings above
$DbPre = $this->dbConfig['dbase'].".".$this->dbConfig['table_prefix'];
$t1 = $DbPre."product_log";
$t2 = $DbPre."product_category";
$sql = "SELECT $t1.prd_cat_id, $t1.prd_id as item, $t1.prd_name as call,
$t2.cat_name as name, $t2.cat_page as page, max($t1.prd_dts)
FROM $t1 join $t2 on ( $t1.prd_cat_id = $t2.cat_id and $t2.cat_rem is null)
WHERE $t1.prd_rem is null GROUP BY $t1.prd_cat_id
ORDER BY $t2.cat_$sortBy $sortHow ;";
$result = $this->dbQuery($sql);
$rs = array();
for($i=0;$i<@$this->recordCount($result);$i++) {
array_push($rs,@$this->fetchRow($result));
}

Joins are most certainly permitted, as far as I know, that can't be stopped unless you don't have rights on the underlying table.

When you setup your mysql db on dreamhost, you would have specified a host name for it to live on.

If so, in your browser, visit it's phpMyAdmin interface by putting the host name into your browsers location bar:

http://db.yourhost.com/

When it asks for your username/password, put in your database username and password you use in your script.

Then go to the 'SQL' tab for your database and you can type your query in there to test it before putting it back in your php program

Of course, use the database query your php generates, not the php code version

That will allow you to experiment with your outcomes until you get results back, then you can put it in your program once you know its working.

One last thing, change :

$t1.prd_name as call,

to

$t1.prd_name as mycall,

As I said in my first post, call is a reserved word in mysql, you can't use it to name a column of your results set.

Thanks so very much for your help on this, herods :) :)

I changed "call" to "call2" and changed "join" to "left join" and that fixed it.

The code works now on each of the servers with:

MySQL 5.0.18
MySQL 4.0.25

Thanks again! :)

no prob.

I'll review your code if you drop me a line - from what I saw in the php, you perhaps could use a few pointers.

It looks like mine did several years ago :)

steven.herod@gmail.com