-2

how do i convert a string like this:

"joe,anna,kwame,kofi"

to: 

"joe","anna","kwame","kofi"

I am trying to use this in an sql statement with an IN clause but i keep getting the error as shown below:

Last SQL query failed:

SELECT * FROM view_salesreport WHERE deleted =0 AND saletype IN (dropoff,pickup)

Can anyone help

Rizier123
  • 58,877
  • 16
  • 101
  • 156
Jspawn007
  • 7
  • 3
  • How are you getting that string in the first place? – Niet the Dark Absol Jan 05 '15 at 16:13
  • 1
    possible duplicate of [PHP/MYSQL using an array in WHERE clause](http://stackoverflow.com/questions/907806/php-mysql-using-an-array-in-where-clause) – David Jan 05 '15 at 16:13
  • Don't try to do it in SQL - do it in your PHP. OR,GIYF http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x http://www.sqlservercentral.com/blogs/querying-microsoft-sql-server/2013/09/19/how-to-split-a-string-by-delimited-char-in-sql-server/ http://sqlperformance.com/2012/07/t-sql-queries/split-strings https://gallery.technet.microsoft.com/scriptcenter/T-SQL-Script-to-Split-a-308206f3 etc etc etc – Mawg says reinstate Monica Jan 05 '15 at 16:16

4 Answers4

1
// split string into array based on commas
// result: array("joe","anna","kwame","kofi")
$arr = explode(",","joe,anna,kwame,kofi");

// create a string with a "?" for each item in the array
// result: ?, ?, ?, ?
$in  = str_repeat('?,', count($arr) - 1) . '?';

// insert $in in SQL query
// result: SELECT * FROM view_salesreport WHERE deleted = 0 AND saletype IN (?, ?, ?, ?)
$sql = "SELECT * FROM view_salesreport WHERE deleted = 0 AND saletype IN ($in)";

// prepares a statement for execution
$stm = $db->prepare($sql);

// executes a prepared statement with array of parameters
$stm->execute($arr);
Simone Nigro
  • 4,717
  • 2
  • 37
  • 72
-1

you can try this:

 $string = "joe,anna,kwame,kofi";
 $string = str_replace("'","\\'",$string);
 $string = "'".str_replace(',',"','",$string)."'";
Luis Simioni
  • 126
  • 6
  • It produces: `'joe'anna'kwame'kofi'` – axiac Jan 05 '15 at 16:31
  • Now try with this: $string = "joe,anna,kwame,kofi,o'brian"; – axiac Jan 05 '15 at 16:37
  • Yes, it still is a problem. Try this: `$string = "joe,anna,kwame,kofi,o\\'brian";`. And even after you quote the backslash, there still is a possibility to produce invalid SQL using this code. For proper quoting use [`mysqli_real_escape_string()`](http://php.net/manual/en/mysqli.real-escape-string.php) or, even better, use prepared statements (see [this answer](http://stackoverflow.com/a/27783508/4265352)) – axiac Jan 05 '15 at 16:55
-1

http://php.net/manual/de/function.explode.php

try something like this:

$output = ''
foreach (explode(',' , $yourString) as $name) {
  $output . = $name
}
gnafgnaf
  • 1
-2

You can easily use explode() and implode() to get the string format you want. For example:

$initial_string = "joe,anna,kwame,kofi";
// split string into array based on commas
$names = explode(',', $initial_string);
// combine back into string using implode
$query = "SELECT * FROM view_salesreport WHERE deleted =0 AND saletype IN ('" . implode("','", $names) .  "')";
Mike Brant
  • 70,514
  • 10
  • 99
  • 103