Category: MySQL

Categories
MySQL PHP Snippet

Quick and Dirty: List records in a table using PHP and MySQL

This code lists reads a dataset from the database and lists all attributes in a table. 

This code sample is deliberately quite basic, though CSS is used (through the $bgcolour variable) to create alternating background colours for the rows.

The code assumes you have already connected to a database.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

echo "<table border='0'>";
// capture the data
$dataset = mysql_query("SELECT * FROM tablename");
// now process the dataset, one row at a time
while ($row = mysql_fetch_assoc($dataset)) {
  // create a variable for each attribute in the record
  //  - variables are easier to use when building the table 
  foreach($row as $var => $value) {
    $$var = $value;
  }
  // set the alternating background colour for the row
  $bgcolour = (!isset($bgcolour) || $bgcolour == "#F0F7FF") ? "#FFFFFF" : "#F0F7FF";
  // build the rows, placing the variables in <td> elements as appropriate
  echo "\n<tr style="background-color:$bgcolour;">";
  echo "\n<td style="font-size:80%;">$attribute</td>";
  echo "\n</tr>";
} // end while
echo "</table>";
Categories
Database MySQL

Selecting Field Names in MySQL

Selecting field names in MySQL is pretty easy, though it's not quite like the SELECT syntax we're all used to:

SHOW COLUMNS FROM TABLE;

To select all fields that start with 'customer' we extend that a little bit, note the wild card to select all fields that start with 'customer'.

SHOW COLUMNS FROM TABLE LIKE 'customer%';
Categories
MySQL PHP

Prevent SQL Injection attacks in PHP and MySQL

Place this code in your database connect include, just before the database connection is made.

This ensures that SQL injection attempts are handled before the database is opened.

By doing it up front like this, the input is already escaped and we don't have to deal with it in our other scripts as we use input data.

1
2
3
4
5
6
7
8
9
10
11
12

// ~~~~~~~~~~~~~~~~~~~~ SECURE ALL INPUT FROM SQL INJECTION ~~~~~~~~~~~~~~~~~~~~~~~~ //
// --- escape special characters in input data to prevent SQL injection attacks ---- //
 
// Prevent SQL Injection attacks in POST vars
foreach ($_POST as $key => $value) {
  $_POST[$key] = mysql_real_escape_string($value);
}
// Prevent SQL Injection attacks in GET vars
foreach ($_GET as $key => $value) {
  $_GET[$key] = mysql_real_escape_string($value);
}
// ~~~~~~~~~~~~~~~~~~~ /secure all input from sql injection ~~~~~~~~~~~~~~~~~~~~~~~~ //
Categories
Database MySQL

Resetting a MySQL Password

Resetting a MySQL root password (or other password, for that matter) is easy enough if you have admin access to the server.

First go to the command line (Start > Run > "cmd") and type "mysqladmin" – and see a bunch of output text, you're good to go. otherwise you'le need to locate mysqladmin.exe and navigate to that folder.

Once there, enter the following command:

mysqladmin -u root password NEWPASSWORD

and you're good to go!

Categories
MySQL

Importing a Spreadsheet or .CSV file into MySQL

Importing a spreadsheet, or its comma separated (CSV) equivalent, into a MySQL database can be enormously challenging and frustrating.

After many attempts at the solutions available through online searches, I determined that the most effective way was to save the spreadsheet in comma-separated format and write a PHP script to read the file row by row and insert the records in the database.

You'll need to adapt this script to your situation, the example below is keyed to the table I've been working with.
[sourcecode language="php"]
";
// ~~~~~~~~~~~~~~~~~~~~~~~~~ /create attribute list ~~~~~~~~~~~~~~~~~~~~~~~~~ //
// ~~~~~~~~~~~~~~~~~~~~~~~~~ PROCESS DATA LINES ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ //
foreach($lines as $line_num => $line) {
 // trim the line – any whitespace can complicate life later
 $line = trim($line);

 // explode the line into an array, splitting it on the commmas in the csv.
 $values = explode(",",$line);
 // prepare the beginning of the INSERT statement
 $insert_statement = "INSERT INTO packages ($attribute_list) VALUES (";
 $insert_statement = "INSERT INTO packages (attribute_one,attribute_two,attribute_three,attribute_4,attribute_5) VALUES (";
 // now loop through the array, adding the elements to the values() section of the insert statement
 foreach ($values as &$value) {
  // according to
  //  http://bytes.com/groups/php/1146-insert-null-not-blank-into-mysql-php-script
  // this will insert null for empty elements
  if ($value) {
   $insert_statement .= "'" . mysql_escape_string($value) . "'";
  } else {
   $insert_statement .= "NULL"; // in the SQL query "NULL" will NOT be quoted
  }
  $insert_statement .= ",";
 }
 // now lop off the trailing string…
 $insert_statement = substr($insert_statement,0,-1);
 // … and close the statement
 $insert_statement .= ");";

 // now we'll either execute the statement or display it onscreen
 // (by default we only display onscreen for debugging – user must pass query string to execute)
 if ($_GET['execute'] == "1") {
  // if the execute instruction is passed in the query string, "?execute=true", execute the insert statement against the database
  $result = mysql_query($insert_statement);
  echo "Inserted record
";
 } else {
  // otherwise, display the insert statement onscreen for debug purposes
  echo $insert_statement;
  echo "
";
 }

}
// ~~~~~~~~~~~~~~~~~~~~~~~~~ /process data lines ~~~~~~~~~~~~~~~~~~~~~~~~~~~ //
?>
[/sourcecode]