Database querry in php

[vood0o]

I have this code line:function update($data) { $q = "SELECT id FROM estates WHERE list_id = '".mysql_real_escape_string(trim($data['list_id']))."'"; $r = $this -> db -> query($q); if ($rw = $r -> fetchRow()) { $eid = $rw['id']; } $q = "UPDATE estates SET title = '".mysql_real_escape_string($data['title'])."', country = '".mysql_real_escape_string($data['country'])."', region = '".mysql_real_escape_string($data['region'])."', zone = '".mysql_real_escape_string($data['zone'])."', city = '".mysql_real_escape_string($data['city'])."', price = '".mysql_real_escape_string($data['price'])."', currency = '".mysql_real_escape_string($data['currency'])."', unit = '".mysql_real_escape_string($data['unit'])."', exclusivity = '".mysql_real_escape_string($data['exclusivity'])."', ad_type = '".mysql_real_escape_string($data['ad_type'])."', estate_type = '".mysql_real_escape_string($data['estate_type'])."', description = '".mysql_real_escape_string($data['description'])."', date_in = '".mysql_real_escape_string($data['date_in'])."', date_out = '".mysql_real_escape_string($data['date_out'])."', rooms = '".mysql_real_escape_string($data['rooms'])."', dorms = '".mysql_real_escape_string($data['dorms'])."', total_mp = '".mysql_real_escape_string($data['total_mp'])."', util_mp = '".mysql_real_escape_string($data['util_mp'])."', marime_lot = '".mysql_real_escape_string($data['marime_lot'])."', agent = '".mysql_real_escape_string($data['agent'])."', office_id = '".mysql_real_escape_string($data['office'])."' WHERE list_id = '".$data['list_id']."'"; //echo "\n\n$q\n\n"; if ($res = $this -> db -> query($q)) { return $eid; } else { return false; } } how can i transform it into php to select from the database all the data from the zone section. In my php file I have this code line : $r = $estate->db->query($q);if ($rw = $r -> fetchRow()) { $tot = $rw['tot'];}$smarty->assign("total_estates",$tot); Please help

[justsomeguy]

If you want to select every value from the zone field, you can use this query: SELECT DISTINCT zone FROM estates You can use a group by if you want to figure how many records have each zone: SELECT zone, COUNT(id) AS num FROM estates GROUP BY zone I'm not sure how you would integrate that query into the rest of your code though.

[vood0o]

i got this code line, it is ok, but i have a problem, i would like to make the top of the list the list_id wsm-61853 and than for the rest of the ids i would like to be ordered random. also how can i make an order by two categories from a table in database. I want the query to show my results by two entries from the zone category(ex. England and Germany) if(empty($orderby)){ $orderby = "ORDER BY e.list_id DESC"; }$q = "SELECT e.*, u.name as agent_name, u.direct_phone as agent_phone,u.email as agent_email, u.id as agent_id FROM estates e LEFT JOIN users u on u.id = e.agent WHERE e.deleted=0 AND e.office_id = 1 AND e.onsite=1 AND e.list_id = 'WSM-61853 ' ".$qplus." $orderby LIMIT ".$l[1].", ".$l[0]; my php file uses a tpl file to get the middle section for the primary page, in that tpl file i can not create another section, to use the first one to show up just the list_id that i want and the other one the rest of estates.

[justsomeguy]

i got this code line, it is ok, but i have a problem, i would like to make the top of the list the list_id wsm-61853 and than for the rest of the ids i would like to be ordered random.

That's 2 queries, one to get the first one and one to get everything except that one. To order randomly you can use a random function in the order by clause, e.g.: ORDER BY RAND(). You could combine them into one query if you use a UNION.

also how can i make an order by two categories from a table in database

I don't know what you mean.

[vood0o]

$q = "SELECT e.*, u.name as agent_name, u.direct_phone as agent_phone,u.email as agent_email, u.id as agent_id FROM estates e LEFT JOIN users u on u.id = e.agent WHERE e.deleted=0 AND e.office_id = 1 AND e.onsite=1 AND e.promote = 'Yes' AND e.estate_type = 'flat' AND e.zone = 'New York' ".$qplus." OR e.zone = 'New Jersey' $orderby LIMIT ".$l[1].", ".$l[0]; Is this query ok, to get as results from the database, estates that are in one of the 2 zones and have the word yes in promote field?

[justsomeguy]

Did you run it to see what happens? It depends what is in the $qplus variable, what you showed isn't a query, it's PHP code to build a query. There could be anything in $qplus that would change how it filters records. You probably want to add parentheses around the AND and OR conditions to make sure it's doing what you expect.

[vood0o]

such as AND ( e.office_id = 1) AND (e.onsite=1) AND (e.promote = 'Yes') AND (e.estate_type = 'flat') AND (e.zone = 'New York') ".$qplus."?

[justsomeguy]

That's exactly the same as the first query, putting parentheses around a single expression doesn't change anything. You need to put parentheses to explicitly separate the AND clauses from the OR clauses. Right now it's going to select records if the zone is New Jersey, regardless of the rest of the data. The reason is because you have .. "OR zone = 'New Jersey'" at the end. So it could be all of those other things, or New Jersey. If that OR is part of another AND then you need to use parentheses to group the conditions properly so it knows what you're asking it for. Without parentheses, with that OR there, that last one stands on its own.

[vood0o]

Ok, thanks for your help, i've done it. it works just fine.