CODEWITHSUNDEEP
phpmysqlarrays
user1050593
user1050593

Reputation:

mysql and php array mixture

I got mysql table like this

id | type | number
1  |  c   |  2
2  |  c   |  10
3  |  c   |  20

Also i got PHP array with values:

$array[c] = 5;
$array[d] = 10;

I wanna do something like this

( SELECT * FROM table WHERE number >= $array[ type ] )

so that type could be taken from mysql column somehow and used for finding correct value from array.

Thats kinda tricky, but I'm not sure how better I could ask.

Upvotes: 1

Views: 97

Answers (4)

Kickstart
Kickstart

Reputation: 21523

Possibly a bit better than using lots of WHERE clauses would be something like this:-

SELECT *
FROM table a
INNER JOIN
    (SELECT 'c' AS RowType, 5 AS RowVal
    UNION
    SELECT 'd', 10) Sub1
ON a.type = Sub1.type AND a.number >= Sub1.RowVal

Set up a subselect which is just getting the constants, and then do a join between that subselect and your existing table.

In php done something like this:-

<?php
$SubQuery = array();
foreach ($array AS $key=>$value)
{
    $SubQuery[] = "SELECT '$key' AS RowType, $value AS RowVal";
}

$sql = "SELECT *
FROM table a
INNER JOIN (".implode(" UNION ", $SubQuery).") Sub1
ON a.type = Sub1.type AND a.number >= Sub1.RowVal";

?>

Upvotes: 0

MatthewMcGovern
MatthewMcGovern

Reputation: 3496

This isn't the most elegant way but something like this?

$where = "WHERE ";

foreach($array as $key => $value)
{
    $where .=  "(type = $key AND number >= $value) OR";
}

$where = substr($where, 0, strlen($where)-2);

You'd have to attach that to your select statement and then run the query obviously.

Hopefully that allows someone else to catch on and provide a more elegant solution.

Upvotes: 1

Anze
Anze

Reputation: 706

try to do it in two steps: before use an sql query to put in an array the values of type

then in a while

  ( SELECT * FROM table WHERE number >= $array[$i] )

where $i is the index of the while loop

Upvotes: 0

Ihor Kharchenko
Ihor Kharchenko

Reputation: 11

Try this:

$type = 'c';
$query = "SELECT * FROM table WHERE number >= " . intval( $array[ $type ] ) . " AND type = '" . $type . "'";

Upvotes: 0

Related Questions