Let's Talk about basic mysql commands used in php.
I will now show you a list of the most common MySQL FUNCTIONS :
QUOTE
mysql_connect(MySQL server name,username,password) - opens a connection to a MySQL server.

mysql_select_db(database name,connection id) - selects a database residing on the MySQL server. The database name parameter referes to an active database on the MySQL server that was opened with the mysql_connect function. The connection identifier is a reference to the current MySQL connection.

mysql_query(sql query) - sends a query to the currently active database.

mysql_fetch_array(recordset id) - Returns an array that corresponds to the fetched row and moves the internal data pointer ahead.

mysql_num_rows(recordset id) - determines the number of rows contained in a recordset returned by the previous SQL SELECT operation. The function returns a value of FALSE if the operation fails.

mysql_affected_rows(connection id) - determines the number of rows affected by the previous SQL INSERT, DELETE, or UPDATE operation. The function returns a value of -1 if the operation fails.

mysql_close(connection id) - closes MySQL connection.


The first thing to always do before combining php/mysql is to connect to the mysql database and the main mysql connection ,
But instead of writing it again and again on each page, heres what i did /am doing for my current game :
CODE
<?php
//connection.php
$connection=mysql_connect("mysql server name","username","password");
$db=mysql_select_db("mysql database name",$connection);
?>


CODE
<?php
//list.php

require('connection.php');

//listing command here

?>



the require("file.fileextension.");
is a command used to include other files into that page you are using. Pretty useful and helpful rather than writing the code over and over again. the require() command automatically puts all the data of the required file into the main page and all other pages it is used in.

BASIC MYSQL COMMANDS:

To executed a command, you have to use mysql_query() .
but i suggest storing it in a variable because that is the recordset id used in most of the mysql functions.

Select syntax:
SELECT * FROM table_name WHERE criteria (example : name='Aldo' or name='$_GET[name]') ORDER BY ... LIMIT ....

Mostly we dont use order by or limit, that will be used later on in another tutorial.
we will also use 2 new commands:
the while () command and a mysql function : mysql_fetch_arrays(r.id);
for now, we won't use while() as it is a bit confusing and we will use the WHERE command in SELECT so that we wont have to use while right now.
DETAILS FOR THIS CODE:
database=anything
table name=users
fields in mysql table `users` :
-name
-age
-email
CODE
<?php
require('connection.php');

$select_mysql_results=mysql_query("SELECT * from `users` WHERE name='Aldo'")or die(mysql_error());
$result=mysql_fetch_array($select_mysql_results);
echo "Name : ".$result['name']." <br />";
echo "Age    : ".$result['age']."<br />";
echo "email  : ".$result['email']." ";
?>


That displays all the data for the name "Aldo"  in the database table "users"
You may have noticed that i put `` for users.
well because sometimes in phpmyadmin or in webhosts, there are tables already used so you have to put `` so that it doesnt get confused and blow the script.
the or die();
command is used to stop the script if the select command doesnt work.
mysql_fetch_array(); gets the required data from the table in the database.

UPDATING and DELETING will be included in the next tutorial.
Now we do INSERTING values.
details:
same table.
different fields:
Name and password
[code]
//form.php

<form action='done.php' method='post'>
Name:<input type='text' name='fname'><br>
Password:<input type='password' name='fpass'><br>
</form>

---------------------------
//done.php

<?php
require('connection.php');
$insert=mysql_query("INSERT into `users` (name,password) VALUES ('$_POST[fname]','$_POST[fpass]')");

header('location:success.php');
?>
-----
//success.php
<?php
echo "Congratulations, you have been registered as a user!";
?>
----


Now in the insert command,
firstly we tell it where (which table) to insert , and then we list what all we want to insert(there may be some fields in the table that you dont what to insert into) thus (name,password) . now we tell it what to insert with the values() command in mysql:
VALUE ('$_POST[thenamegiventoitintheform]','$_POST[namegivenintheform]')
but remember, when doing values, you have to insert it in the order you gave for what you want to insert,
like in the code i type name first and pass second.
so you have to type in values the $_POST for name first then password second.

Just thought id explain the header() command to those who dont know it.
Usually, when we put the insert command and then the echo command to tlel someone it has been done, they can easily, press refresh and the information will be sent again, post or get.

so i have been using header to first insert the values then redirect to a page that says success.
SYNTAX:
header('location:file.php');

Hope this clears up some doubts atleast if not all.
Any questions, post here and ill be happy to help smile.gif

 

 

 


Reply