CODEWITHSUNDEEP
phparrays
bateman_ap
bateman_ap

Reputation: 1921

Create SQL statement in PHP function using an Array

I am creating a PHP function that will take some values, one of which is an array, that I need to use in a MySQL query.

I am creating the array as follows:

 $newsArray = createArticleArray(array(2,20,3),5);

Then the function looks something like this (cut down for readability)

 function createArticleArray($sectionArray = array(1),$itemsToShow) 
 { 
 $SQL = "
  SELECT 
    *
  FROM
    tbl_section
  WHERE
    (tbl_section.fld_section_uid = 2 OR tbl_section.fld_section_uid = 20 OR tbl_section.fld_section_uid = 3)
  ORDER BY
    tbl_article.fld_date_created DESC LIMIT 0,$itemsToShow";
 }

The section tbl_section.fld_section_uid = 2 OR tbl_section.fld_section_uid = 20 OR tbl_section.fld_section_uid = 3 is where I need to use the array values.

Basically I need to loop through the values in the array making up that part of the query, however I am having a little problem on how to show or not show the "OR" bits of it as there might be only 1 value or as many as I need.

I was thinking of something like this:

 foreach($sectionArray as $section)
 {
   $sqlString = $sqlString . "tbl_section.fld_section_uid = $section OR";
 }

but I don't know how to work out if to put the "OR" in there.

Upvotes: 0

Views: 2655

Answers (5)

William
William

Reputation: 15593

function createArticleArray($sectionArray = array(), $itemsToShow) {
    $conditions = array();
    for ($i = 0, $s = count($sectionArray); $i < $s; ++$i) {
        $conditions[] = 'tbl_section.fld_section_uid = ' . (int) $sectionArray[$i];
    }
    $SQL = 'SELECT * FROM tbl_section WHERE ' . implode(' OR ', $conditions) . ' ORDER BY tbl_article.fld_date_created DESC LIMIT 0, ' . (int) $itemsToShow;
}

Upvotes: 0

Alin P.
Alin P.

Reputation: 44346

Use implode.

$conditionParts = array(); 
foreach($sectionArray as $section){
   $conditionParts[] = "tbl_section.fld_section_uid = $section";
}
$sqlString .= implode(' OR ', $conditionParts);

This solution answers your question and show you how to use the implode function, but for your specific case you should really use the IN operator.

$sqlString .= "tbl_section.fld_section_uid IN(".implode(',', $sectionArray).")";

Upvotes: 2

Joe Lee
Joe Lee

Reputation: 1

Use PDO's prepare method: https://www.php.net/manual/en/pdo.prepare.php

$statement = $pdo->prepare("
  SELECT 
    *
  FROM
    tbl_section
  WHERE
    (tbl_section.fld_section_uid = ? OR tbl_section.fld_section_uid = ? OR tbl_section.fld_section_uid = ?)
  ORDER BY
    tbl_article.fld_date_created DESC LIMIT 0,$itemsToShow");

$statement->execute( $sectionArray );

Upvotes: 0

user229044
user229044

Reputation: 239302

The query can be made simpler and easier to generate if you use WHERE <column> IN (value1,value2,...) syntax.

Use PHP's implode to produce the (value1,value2,...) part:

$SQL .= ' WHERE tbl_section.fld_section_uid IN (' . implode(',', $array) . ') ';

Yields something like this:

SELECT
...
WHERE tbl_section.fld_section_uid IN (2,20,3)
...

Upvotes: 1

Victor Nicollet
Victor Nicollet

Reputation: 24577

One solution is to put an extraneous 0 at the end to consume the final "OR" without any effect. The query parser will just remove it: A OR B OR C OR 0 is turned into A OR B OR C.

Another solution is to use implode to insert the OR: 

$sqlString = "tbl_section.fld_section = "
 . implode($sectionArray," OR tbl_section.fld_section_uid = ");

Of course, the correct solution is just to use IN: 

"WHERE tbl_section.fld_section_uid IN(".implode($sectionArray,',').")";

Upvotes: 1

Related Questions