Home arrow MySQL arrow Page 3 - The Three Most Important MySQL Queries

The SELECT MySQL Query Command - MySQL

Doing PHP queries to a MySQL database is one of the most important processes in any web application. This is where data is being fetched from or inserted into the database. If you are a beginning PHP web developer, then learning the most important MySQL queries is essential to your success in dealing with dynamic websites.

TABLE OF CONTENTS:
  1. The Three Most Important MySQL Queries
  2. The INSERT MySQL Query Command
  3. The SELECT MySQL Query Command
  4. The UPDATE MySQL Query Command
By: Codex-M
Rating: starstarstarstarstar / 27
July 06, 2009

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

So we now have data in our database. If we would like to grab data from it, we will need the SELECT command. There are several options for grabbing data.

Option 1: Grabbing the entire data table out of the database:

SELECT * FROM `tablename`

Here is the PHP script to illustrate this example. It grabs the entire MySQL data table and displays it in your browser.

<?php

//Step 1 Connect to database

$username = "Your MySQL username here";

$password = "Your MySQL password";

$hostname = "Hostname";

$table = "MySQL Table name where the data will be inserted";

$database = "The name of the MySQL database which holds the table";

$dbhandle = mysql_connect($hostname, $username, $password)

or die("Unable to connect to MySQL");

$selected = mysql_select_db($database,$dbhandle)

or die("Could not select $database");

//Step 2. Insert other PHP scripts here (such as grabbing data from HTML forms, etc)

//Step 3. Sanitize variables before inserting to database. This will prevent MySQL injection.

$categories_id = mysql_real_escape_string(stripslashes($categories_id));

$language_id = mysql_real_escape_string(stripslashes($language_id));

$categories_name = mysql_real_escape_string(stripslashes($categories_name));

$result = mysql_query("SELECT * FROM `categories_description`")

or die(mysql_error());

echo '<table border="1">';

echo '<tr>';

echo '<td><b>Categories ID</b></td>';

echo '<td><b>Language ID</b></td>';

echo '<td><b>Categories Name</b></td>';

echo '</tr>';

while($row = mysql_fetch_array($result)){

echo '<tr>';

echo '<td>'.$row['categories_id'].'</td>';

echo '<td>'.$row['language_id'].'</td>';

echo '<td>'.$row['categories_name'].'</td>';

echo '</tr>';

}

echo '</table>';

?>

This will display the table in the HTML browser, which looks like this:

Option 2: Extracting specific data from a specific fieldname in the MySQL table.

There are cases when you need to extract specific data from a specific fieldname that satisfies certain conditions.

An example of this application is when you need to extract the customer email address if they can correctly provide the username and password. So there could be three fields in the table: username, password, and emailaddress.

So, to extract the customer email address in a MySQL table, for example customertable, the PHP query could be:

<?php

//Connect to database...

//Sanitize the variables..

//MySQL query

$result = mysql_query("SELECT `emailaddress` FROM `customertable` WHERE `username`='$username' AND `password`='$password'")

or die(mysql_error());

$row = mysql_fetch_array($result)

or die("Invalid query: " . mysql_error());

$emailaddress = $row['emailaddress'];

echo "Your email address is: $emailaddress";

?>

In this PHP MySQL query, the WHERE statement specifies the condition under which it pulls out an email address matching a certain username AND a password.

You can query MySQL using several AND statements, as long as it is correctly done.



 
 
>>> More MySQL Articles          >>> More By Codex-M
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: