current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
add comment

5 Answers

up vote 2 down vote accepted

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).")";
share|improve this answer
 
Many thanks all for your help, works perfectly –  bateman_ap Nov 11 '10 at 15:13
add comment
up vote 1 down vote

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,',').")";
share|improve this answer
 
+1 for 3 in 1 solution –  Alin Purcaru Nov 11 '10 at 15:19
add comment
up vote 1 down vote

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)
...
share|improve this answer
 
You've missed a couple of parentheses. –  Alin Purcaru Nov 11 '10 at 15:07
 
@Alin Thanks, fixed –  meagar Nov 11 '10 at 15:08
add comment
up vote 0 down vote

Use PDO's prepare method: http://uk3.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 );
share|improve this answer
add comment
up vote 0 down vote 
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;
}
share|improve this answer
 
Those are conditions, not clauses. SELECT, FROM, WHERE are clauses. –  Alin Purcaru Nov 11 '10 at 15:09
 
Good point, not thinking correctly this morning. –  William Nov 11 '10 at 15:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.