function clean_array_for_post($post) {
foreach($post as $key => $value) {
// stripslashes, we don't want to rely on magic quotes
if(get_magic_quotes_gpc()) {
$post[$key] = stripslashes($value);
}
// quote if not a number
if(!is_numeric($value)) {
$post[$key] = mysql_real_escape_string($value);
}
}
return $post;
}
Tag Archive for mysql
Clean array for Posting / SQL injection protection
Turn MySQL results into an array, the proper way!
while($rows[] = mysqli_fetch_assoc($result)); array_pop($rows); // pop the last row off, which is an empty row
Drop down option in field for MySQL
Campo ENUM ('-','D','E','L','S') NOT NULL
MySQL dump using SSH on Mac Terminal
ssh <a href="mailto:username@domain.com.au">username@domain.com.au</a> mysql -u username_db -p username_db < mysql_dump/db_to_be_imported.sql
The Worst Performing Indexes
/* SQL script to grab the worst performing indexes in the whole server */ SELECT t.TABLE_SCHEMA AS `db` , t.TABLE_NAME AS `table` , s.INDEX_NAME AS `inde name` , s.COLUMN_NAME AS `field name` , s.SEQ_IN_INDEX `seq in index` , s2.max_columns AS `# cols` , s.CARDINALITY AS `card` , t.TABLE_ROWS AS `est rows` , ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS `sel %` FROM INFORMATION_SCHEMA.STATISTICS s INNER JOIN INFORMATION_SCHEMA.TABLES t ON s.TABLE_SCHEMA = t.TABLE_SCHEMA AND s.TABLE_NAME = t.TABLE_NAME INNER JOIN ( SELECT TABLE_SCHEMA , TABLE_NAME , INDEX_NAME , MAX(SEQ_IN_INDEX) AS max_columns FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA != 'mysql' GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME ) AS s2 ON s.TABLE_SCHEMA = s2.TABLE_SCHEMA AND s.TABLE_NAME = s2.TABLE_NAME AND s.INDEX_NAME = s2.INDEX_NAME WHERE t.TABLE_SCHEMA != 'mysql' /* Filter out the mysql system DB */ AND t.TABLE_ROWS > 10 /* Only tables with some rows */ AND s.CARDINALITY IS NOT NULL /* Need at least one non-NULL value in the field */ AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00 /* Selectivity < 1.0 b/c unique indexes are perfect anyway */ ORDER BY `sel %`, s.TABLE_SCHEMA, s.TABLE_NAME /* Switch to `sel %` DESC for best non-unique indexes */ LIMIT 10;
mysql query connect function
function query($sql){
$dbhost = "localhost";
$dbuname = "root";
$dbpass = "root";
$dbname = "disco_conv";
$conxion = mysql_connect($dbhost, $dbuname, $dbpass);
$result=mysql_db_query($dbname,$sql,$conxion);
return $result;
}
How to install a gzipped mysqldump on a local Mamp server
zcat <DATABASE>.sql.gz.Z | /Applications/MAMP/Library/bin/mysql --host=localhost --port=8889 --user=root --password=root <DATABASE>
MySQL backup using mysqldump
mysqldump --opt --user=USER_NAME --password DB_NAME > DB_NAME.sql mysql --user=USER_NAME --password DB_NAME < DB_NAME.sql
PHP/MySQL – Get Table Data
function tabulardata($tablename,$columnstring){
$columns = explode("|",$columnstring);
$field_query = mysql_query("SELECT * FROM ".$tablename."");
if(mysql_num_rows($field_query) > 0){
//Get field data
echo("<table>
");
echo("<tr>
");
$columnnames = array();
foreach($columns as $column){
if(strstr($column,"=")){
$columninfo = explode("=",$column);
$columntitle = $columninfo[1];
$columnnames[] = $columninfo[0];
}else{
$columntitle = $column;
$columnnames[] = $column;
}
echo("<td>".$columntitle."</td>
");
}
echo("</tr>
");
while($field_array = mysql_fetch_array($field_query)){
echo("<tr>
");
foreach($field_array as $field_key=>$field_value){
if(in_array($field_key,$columnnames) && $field_key != ""){
echo("<td>".$field_value."</td>
");
}
}
echo("</tr>
");
}
echo("</table>");
}
}
PHP/MySQL – Get Row Data
function getrowdata($tablename,$rowid,$prefix){
$field_query = mysql_query("SELECT * FROM ".$tablename." WHERE id = ".$rowid."");
if(mysql_num_rows($field_query) > 0){
//Get field data
$field_array = mysql_fetch_array($field_query);
foreach($field_array as $field_key=>$field_value){
//Setup a global variable name for this data based on prefix
global ${"{$prefix}$field_key"};
${"{$prefix}$field_key"} = stripslashes($field_value);
}
}
}