How to use php variables (arrays) in mysql select statements?

Question

Hi everybody,

How do you use a php variable (array) inside a mysql select statement?

I am designing an auction site and currently working on a page that lets people view a list of all the current bids for an item. I want to display 3 columns:

1. amountbid - the amount each bidder has bid for the item (held in tblbid)

2. bidderid - the id of each bidder who has bid (found in tbluser)

3. total_positivity_feedback - how many users have left positive feedback for the bidder (calculated from tblfeedback)

To find the 'amount' and the 'bidderid' columns i pass the essayid URL parameter from the previous page. This works fine.

Despite this, i can't display the total_positivity_feedback column for the various users who have made each bid.

My mysql query looks like this:

select
tblbid.bidderid, 
tblbid.amount,
(select count(tblfeedback.feedbackid) from tblfeedback WHERE tblfeedback.writerid = "ARRAY VARIABLE GOES HERE") AS total_positivity_feedback FROM tblbid WHERE tblbid.essayid = $essayid_bids

I assume that the only way to accomplish this is to make the variable contain the bidderid's of those people who bid for that particular essay? I can't seem to work out how to do this tho?!?

Many thanks for your help

Answer 1

I dont think you will be able to use an array like that. You will have to make use of an IN Clause.

Have a look at SQL IN Operator

So you will have to construct a list of your values, lets say something like

SELECT *
FROM yourTable
WHERE yourID IN (1,2,3,4,5,6)

Answer 2

It's not obvious from your problem where the Writer ID ought to come from. When you say "array", do you mean a PHP array or are you referring multiple values stored in rows in the database? Your description suggests the former as you mention nothing about Writers in the database structure, but I suspect that you mean the latter.

A value from an array can be insert into a PHP string quite simply:

$arr = array("String");
echo "{$arr[0]}";

As I mentioned previously, I suspect that this won't solve your problem. Could you please clarify the relation between the Bids and the Writers? If you are trying to get the feedback on each bidder then you'll wanting something like:

SELECT
    tblbid.bidderid, 
    tblbid.amount,
    (SELECT COUNT(tblfeedback.feedbackid) from tblfeedback WHERE tblfeedback.writerid = tblbid.bidderid) AS total_positivity_feedback
FROM tblbid WHERE tblbid.essayid = $essayid_bids;

Otherwise please explain further.

Answer 3

As suggested by astander, it appears you may benefit from the IN clause of MySQL. A quick way to generate the (1,2,3,4,5,6) string would be use the php function implode().

for example:

$array = array(1,2,3,4,5);
$in = '(' . implode(',', $array) . ')';
echo $in;

yields:

(1,2,3,4,5)

so..

$array = array(1,2,3,4,5);
$in = '(' . implode(',', $array) . ')'; 
$query = sprintf('select tblbid.bidderid, tblbid.amount, (select count(tblfeedback.feedbackid) from tblfeedback WHERE tblfeedback.writerid IN %s AS total_positivity_feedback FROM tblbid WHERE tblbid.essayid = %s', $in, $essayid_bids);