Mysql select query with multiple id

Question

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 ?

what is the error message you get?

amitchhajer
– amitchhajer

2013-03-16 05:50:37 +00:00
Commented Mar 16, 2013 at 5:50 — amitchhajer
– amitchhajer, Commented Mar 16, 2013 at 5:50
You should say what error you're getting

rantsh
– rantsh

2013-03-16 05:50:51 +00:00
Commented Mar 16, 2013 at 5:50 — rantsh
– rantsh, Commented Mar 16, 2013 at 5:50

Mahmoud Gamal · Accepted Answer · 2013-03-16 05:57:35Z

5

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.

edited Mar 16, 2013 at 5:57

answered Mar 16, 2013 at 5:51

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

novalagung · Accepted Answer · 2017-04-20 07:34:24Z

2

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) . ")");

edited Apr 20, 2017 at 7:34

answered Mar 16, 2013 at 5:52

novalagung

11.6k6 gold badges63 silver badges89 bronze badges

Comments

Tucker · Accepted Answer · 2013-03-16 05:53:59Z

1

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) . ")");

answered Mar 16, 2013 at 5:53

Tucker

7,38210 gold badges40 silver badges61 bronze badges

Comments

Sean · Accepted Answer · 2013-03-16 05:58:51Z

1

$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..
}

edited Mar 16, 2013 at 5:58

answered Mar 16, 2013 at 5:51

Sean

12.4k3 gold badges31 silver badges49 bronze badges

Collectives™ on Stack Overflow

Mysql select query with multiple id

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related