[RESOLVED] Arrays in SQL statements

[meigwil]

I have a number of values in an array, what is the best SQL statement that will compare values in a table with each array value?

Something along the lines of:

$sql = "SELECT safle, enw_parti, 'cangen a chylch' FROM canlyniadau ".
	   "WHERE RhifRhestrTestunau = " all values in the array...

Mei

[laserlight]

If the column is numeric, then use:

$sql = "SELECT safle, enw_parti, [cangen a chylch]
	FROM canlyniadau
	WHERE RhifRhestrTestunau IN (" . implode(', ', $array) . ")";

If not, you should use:

$sql = "SELECT safle, enw_parti, [cangen a chylch]
	FROM canlyniadau
	WHERE RhifRhestrTestunau IN ('" . implode("', '", $array) . "')";

[meigwil]

You are the JediMaster.

Thanks very much.

Mei

[meigwil]

One more thing, when a column name has spaces, (cangen a chylch in this example), should I use the [square brackets]? I have received a syntax error.

Complete sql statement:

"SELECT enw_parti, [cangen a chylch] FROM canlyniadau AS c ".
"INNER JOIN rhaglen AS r ".
"WHERE c.RhifRhestrTestunau IN (".implode(', ', $result_numbers).") ". 
"AND r.statws IN (3, 4, 5, 6, 7, 8) ";

Error message:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[cangen a chylch] FROM canlyniadau AS c INNER JOIN rhaglen AS r WHERE c.RhifRhes' at line 1

Mei

[cahva]

Use ` around fieldnames(you can also use them around table names).

[meigwil]

Thanks. I did try that to start with but it gave me an error, which is why I posted here.

But it worked fine this time!

Thanks again,

Mei

[laserlight]

One more thing, when a column name has spaces, (cangen a chylch in this example), should I use the [square brackets]?

Generally, yes. Alternatively you can use double quotes (but not single quotes, which are used for values, not identifiers as in this case).

I have received a syntax error.

That's probably because MySQL should be configured to allow them. MySQL uses the non-standard backticks (`) instead, by default.

[meigwil]

Hmm. It didn't like the [square brackets], so I tried backticks which worked fine.

Cheers,

Mei