CODEWITHSUNDEEP
phpmysqlsqljoin
Babu Ahmed
Babu Ahmed

Reputation: 121

Mysql select query with multiple id

Well, I've a Mysql table(oc_calendar) like this:

id      start_date         username
1       2013-04-04         18
2       2013-03-31         19
3       2013-04-04         19
4       2013-04-02         19 
5       2013-04-03         18

With following query I can get all id based on search result:

$search = mysql_query("SELECT DISTINCT username FROM oc_calendar WHERE start_date 
between '$from' AND '$to'");

while($re_search = mysql_fetch_array($search)) {

$search_p_id = $re_search['username']; // here is the result Like: 18, 19 etc..
}

Now I'm trying to get another table(property_step1) information based on variable $search_p_id with following query but It's show error. May be my query is not correct.

Note: Variable $search_p_id can be 18 or 19, 18 etc..

$search = mysql_query("SELECT * FROM property_step1 WHERE propertyid IN (" . 
implode(',', $search_p_id) . ")");

Why it's show the error message, Any Idea ?

Upvotes: 1

Views: 8370

Answers (4)

novalagung
novalagung

Reputation: 11502

You cannot use implode() to non array data. you shoul change the value of $search_p_id value as array.

$search = mysql_query("SELECT DISTINCT username FROM oc_calendar WHERE start_date between '$from' AND '$to'");
$search_p_id = array();

while($re_search = mysql_fetch_array($search)) {
    $search_p_id []= $re_search['username']; // here is the result Like: 18, 19 etc..
}

$search = mysql_query("SELECT * FROM property_step1 WHERE propertyid IN (" . implode(',', $search_p_id) . ")");

Upvotes: 2

Sean
Sean

Reputation: 12433

$search_p_id currently is not an array, so implode(',', $search_p_id) is most likely failing.

change how you set $search_p_id

while($re_search = mysql_fetch_array($search)) {
  $search_p_id[] = $re_search['username']; // here is the result Like: 18, 19 etc..
}

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

There is no need for two queries, you can do this with only one query, by JOINing the two tables:

SELECT p.* 
FROM oc_calendar AS o
INNER JOIN  property_step1 AS p ON o.username = p.propertyid
WHERE o.start_date between '$from' AND '$to';

I assumed the join condition: o.username = p.propertyid since you get the list of username values from the first table then select all the rows from the second value where the propertyid in the list of username values selected from the first table using the IN predicate. So, I used JOIN it is like the predicate IN but it is safer, and let you select columns from both tables.

Note that: Please Stop using the Mysql_* extensions, your code this way is open to SQL Injection, it is also deprecated. Use PDO or prepared statements instead.

Upvotes: 5

Tucker
Tucker

Reputation: 7362

you're firstly using deprecate mysql api, switch to pdo as Mahmoud says.

Secondly you're using the implode function on $search_p_id on a non array which can perhaps give you undesirable result. Try the following instead:

$search = mysql_query("SELECT DISTINCT username FROM oc_calendar WHERE start_date 
between '$from' AND '$to'");
$search_p_id = array();
while($re_search = mysql_fetch_array($search)) {
   array_push($search_p_id, $re_search['username']);
}   

search = mysql_query("SELECT * FROM property_step1 WHERE propertyid IN (" . 
implode(',', $search_p_id) . ")");

Upvotes: 1

Related Questions