Substr()

PHP
1

Hi,

Im not understanding what substr() is doing in the code below?

The query works if the “code” field is a number (int) but wont work if the field holds numbers and letters… I am trying to make the query work when the “code” field has numbers and letters…


	$query = 'SELECT * FROM products WHERE code IN (';
	foreach ($_SESSION['cart'] as $key => $value) {
	$query .= $key . ',';
	}
	$query = substr ($query, 0, -1) . ')';
	$result = mysql_query($query);

	
	$total = 0;
	while ($row = mysql_fetch_array ($result)) {

Thanks

2

The substr is removing the last comma from the $query.

The foreach is taking all of the keys in $_SESSION[‘cart’] (which seem to be numeric), and concatenating them to ‘SELECT * FROM products WHERE code IN (’ with a comma after each key.

So if you had 3 items in your cart with keys [0], [1], [2], your query after the foreach would be
‘SELECT * FROM products WHERE code IN (0,1,2,’

Then the substr, removes the last character in the query, so now the query becomes and concatenates the closing parenthesis
‘SELECT * FROM products WHERE code IN (0,1,2)’

You are correct in that the query does NOT support the key having characters, it must be numeric.

Hope that helps

3

Ahh yea that makes sence…

$_SESSION[‘cart’] doesnt that hold the $code and the key? and not taking the value into account?

4

Yes and Yes.

5

Ok… im abit lost… can the query be writen in another way so I can use the code field that holds numeric and characters? aposed to say id field that holds numeric?

6

Does the code column in your database allow numbers and characters? If it doesn’t I’d need to know what column you’d actually want to query against.

7

Yea the code column holds numbers and characters… and I want to use that column and drop the id column as it makes it hard to keep my items up dated with csv file… as the code never changes but the id field often changes when products are added and removed in the csv file

8

Got it, okay, here you go

	$query = 'SELECT * FROM products WHERE code IN (';
	foreach ($_SESSION['cart'] as $key => $value) {
	$query .= "'" . $key . "',";
	}
	$query = substr ($query, 0, -1) . ')';
	$result = mysql_query($query);

	
	$total = 0;
	while ($row = mysql_fetch_array ($result)) {

Now it should produce a query similar to
“SELECT * FROM products WHERE code IN (‘0’,‘1’,‘2’)”

Thus now that the $key’s are in quotes, it can accept characters as well as numbers.

9

I did try quotes as it seemed like the answer just like a standard query, no quotes needed around numbers… But placed the quotes in all the wrong places…

Thanks…

10

Not sure I understand your statement, being that code is a varchar type field (both letters and numbers), you should have used quotes in your query all of the time.

11

I used to use “id” which was int field, but now want to use code field instead which is varchar…

12

as a side note, that could be simplified a bit using [FPHP]implode[/FPHP] and [FPHP]array_keys[/FPHP]:

$query = 'SELECT * FROM products WHERE code IN ('.implode(',',array_keys($_SESSION['cart'])).')';

and for non-numerics:

$query = 'SELECT * FROM products WHERE code IN ("'.implode('","',array_keys($_SESSION['cart'])).'")';

NOTE: as always, values should be sanitized.

13

Thanks… Looks like a good option.