Selecting from MySql

[12many]

Hi There

Im new to php and im writing a webservice that communicates with a My sql database, when i select database all the examples do it like this

$result = mysql_query('select * from Hire_Codes');
	if (mysql_num_rows($result) > 0)
	{
		$response["equip"] = array();
		While ($row = mysql_fetch_array($result))
		{
			$equip = array();
			$equip["Code"] = $row["Code"];
			$equip["Description"] = $row["Description"];
			array_push($response["equip"], $equip);
		}
		$response["Success"] = 1;
		echo json_encode($response);	
	}

which is fine but...
what if i don't want to have to hard code my column names?? basically i want to be able to use something similar to this that can handle a posted MySql Select statement.
is it possible to extract the column name from row in this instance?

Many thanks

Ian

[penagate]

$response['equip'] = [];
while ($row = mysql_fetch_assoc($result))
  $response['equip'][] = $row; 

[penagate]

Notes:

— You're using the old MySQL library. If you've just got started, stop now: I recommend PDO instead.

$dbh = new PDO('mysql:dbname=example;host=example');

// ...

$response['equip'] = [];
foreach ($dbh->query('select * from Hire_Codes') as $row)
  $response['equip'][] = $row; 

Aside from being more modern, its chief advantage is that it supports query parameters:

$st = $dbh->prepare('select * from example where foo=:foo');
$result = $st->execute([
  ':foo' => 'parameter value goes here'
]); 

Parameters allow you to insert values into queries without worrying about correctly escaping characters such as quotes and backslashes; this avoids the risk of introducing SQL injection vulnerabilities to your code.


— This sounds dangerous:

handle a posted MySql Select statement

This might work if the web service is only used in something like an intranet, where you control the clients as well. In general it's poor form to execute arbitrary SQL. Instead your web service should expose an interface made up of clearly-defined methods which each correspond to a particular query. If you elaborate on what you're building I could perhaps help you a bit more here.

[12many]

Hi Penagate

Thanks for the reply that looks great. on thing though is there a way Using PDO to count how many rows there are in the result. Ive had a really good look online and it seems pretty unclear

Ian

[penagate]

A couple of ways. You can try the PDOStatement::rowCount property, but note the caveat described on that page which is that not all database systems will return the number of rows after a SELECT statement. Instead, they suggest executing a SELECT COUNT(*) first, followed by the SELECT.

Personally, if you need to count the rows before parsing each one, I would do something like this:

if ($st->execute() === true)
{
  $rows = $st->fetchAll(PDO::FETCH_ASSOC);
  echo count($rows);
} 

If you don't need the count upfront, then:

$count = 0;
if ($st->execute() === true)
{
  while ($row = $st->fetch(PDO::FETCH_ASSOC))
  {
    ++$count;
    // ...
  }
}