Where clause

[SilverDragonTears]

How do I do a where clause for this if I only want adopts that have the value 'shop' for the field whenisavail to show up?

$stmt = $adopts->join("adoptables_conditions", "adoptables_conditions.id = adoptables.id")
->select("adoptables", array());

[Hall of Famer]

Its quite easy, do this:


$stmt = $adopts->join("adoptables_conditions", "adoptables_conditions.id = adoptables.id") ->select("adoptables", array(), "whenisavail = 'shop'");


The select() method accepts three arguments: table, columns and whereclause. The table is quite obvious, while columns need to be an array of what columns you want to fetch(pass an empty array to fetch all columns). The whereclause is whatever is after the WHERE keyword in an sql statement.

[SilverDragonTears]

See, I tried that before I posted and I got this:

Fatal error: Uncaught exception 'Exception' with message 'Database error 1052 - Column 'whenisavail' in where clause is ambiguous' in /home/taleofdr/public_html/classes/class_database.php:161 Stack trace: #0 /home/taleofdr/public_html/classes/class_database.php(81): Database->_query('adoptables', Array, 'select', 'whenisavail = '...') #1 /home/taleofdr/public_html/dshop.php(25): Database->select('adoptables', Array, 'whenisavail = '...') #2 {main} thrown in /home/taleofdr/public_html/classes/class_database.php on line 161

[Hall of Famer]

Oh I see, this is because whenisavail field is present in both columns. The trick here is:


$stmt = $adopts->join("adoptables_conditions", "adoptables_conditions.id = adoptables.id")
->select("adoptables", array(), constant(PREFIX)."adoptables.whenisavail = 'shop'");

[SilverDragonTears]

Warning: constant() [function.constant]: Couldn't find constant adopts_ in /home/taleofdr/public_html/dshop.php on line 26

Fatal error: Uncaught exception 'Exception' with message 'Database error 1054 - Unknown column 'adoptables.whenisavail' in 'where clause'' in /home/taleofdr/public_html/classes/class_database.php:161 Stack trace: #0 /home/taleofdr/public_html/classes/class_database.php(81): Database->_query('adoptables', Array, 'select', 'adoptables.when...') #1 /home/taleofdr/public_html/dshop.php(26): Database->select('adoptables', Array, 'adoptables.when...') #2 {main} thrown in /home/taleofdr/public_html/classes/class_database.php on line 161

[Hall of Famer]

Oh my, forgot to enclose PREFIX by double quote. The below code should work:


$stmt = $adopts->join("adoptables_conditions", "adoptables_conditions.id = adoptables.id")
->select("adoptables", array(), constant("PREFIX")."adoptables.whenisavail = 'shop'");

[SilverDragonTears]

What in the world? The one adopt I have to test it isn't showing up :/

[Hall of Famer]

umm you are trying to add a new condition called 'Shop'? Id say look into the functions_adopts.php and see if you can find any clues. As it is now the adoptables conditions are hard-coded, which means creating your own is not easy. You have to modify the core script.

[SilverDragonTears]

No I just named it that so I could differentiate it from the others.

[Hall of Famer]

umm thats weird, the adoptables should show up then. First check your database to see if the field 'whenisavail' indeed has the value 'shop', and then see if the adoptable ids are the same for this pet in table prefix.adoptables and prefix.adoptables_conditions. Gloometh and Iris both had problems with the pets id being different in the two tables, and for this reason the adoptables wont show.

[SilverDragonTears]

Wow. The id's were different. What causes that?

[Hall of Famer]

It was because you reset the auto_increment from phpmyadmin in adoptables table, but not in adoptables_conditions. I strongly recommend against resetting auto_increment unless you know what you are doing.

[SilverDragonTears]

I didn't reset anything unless I did it on accident.