Jan 21 2010

MYSQL add counter in a query

Category: MySql,Php — Giulio Pons @ 12:13 am

Sometimes I’ve needed to add a counter column in a query and I don’t want to add the column with php, so I’ve found this query to put the counter directly in the record set returned out from the query.

set @N = 0;
SELECT @N := @N +1 AS number, name, surname FROM people;

To use it in PHP scripts you have to use 2 query statements:

mysql_query("set @N = 0;");
$rs = mysql_query("SELECT @N := @N +1 AS number, name, surname FROM people");
while ($r=mysql_fetch_array($rs)) {
	echo $r['number']." - ".$r['name']." ".$r['surname']."<br/>";
}
Share

Tags: , , , ,

Jan 21 2010

Using REPLACE in an UPDATE statement to fix data on MSSQL and MySQL

Category: MsSQL,MySql — Giulio Pons @ 12:09 am

Sometimes it’s necessary fix data on the database.
I had to fix on a Microsoft database:

      /* MSSQL syntax */
      update table_links set url  = Replace(url, 'www.link.it/web3', 'www.link.it/web') where url <>''

I had to fix on a MySQL database:

      /* MySQL syntax is the same! */
      UPDATE table_links SET url =REPLACE(url, '&amp;', '&') where url<>''

The syntax is the same.

Share

Jan 19 2010

PHP to get enum/set values from mysql field

Category: MySql,Php — Giulio Pons @ 10:36 am

This function returns an array with the elements allowed in a ENUM or SET mysql field. This can be usefull if you’re making some automation and need to retrieve those values without writing them in your code:

function set_and_enum_values( $table , $field ){
	$query = "SHOW COLUMNS FROM `$table` LIKE '$field'";
	$result = mysql_query( $query ) or die( 'Error getting Enum/Set field ' . mysql_error() );
	$row = mysql_fetch_array($result);
	if(stripos(".".$row[1],"enum(") > 0) $row[1]=str_replace("enum('","",$row[1]);
		else $row[1]=str_replace("set('","",$row[1]);
	$row[1]=str_replace("','","\n",$row[1]);
	$row[1]=str_replace("')","",$row[1]);
	$ar = split("\n",$row[1]);
	for ($i=0;$i<count($ar);$i++) $arOut[str_replace("''","'",$ar[$i])]=str_replace("''","'",$ar[$i]);
	return $arOut ;
}
Share

« Previous Page — Next Page »