Mysql user guide – useful instructions / fetch count rows

Renuméroter une table avec auto increment
set @i=0;
update myvideos set id=(@i:=@i+1);
ALTER TABLE myvideos AUTO_INCREMENT = 1;

Arrangement de zones :

ALTER table Auteurs
MODIFY COLUMN AuteurID  mediumint(8)
AFTER notice_ref

ALTER TABLE Auteurs
MODIFY COLUMN notice_ref mediumint( 8 )
AFTER `AuteurID`
————————————————
RENAME TABLE `jarid`.`a030` TO `jarid`.`Auteurs` ;

Détecter les doublons :

SELECT * , count( * )
FROM tisn
GROUP BY isn
HAVING count( * )>1

SELECT * , count( * )
FROM a220
GROUP BY notice_ref
HAVING count( * ) >1
ORDER BY notice_ref DESC

SELECT DISTINCT a220
FROM `a220`

Synchronisation de strutures de tables champs par champs :

INSERT INTO jarid (notice_id) SELECT isn FROM tisn;

UPDATE jarid j, tia000 t
SET j.a020 = t.A000
WHERE j.notice_id = t.notice_id

SELECT     ISN, Ville, Occ, Diffuseur, Editeur
FROM         TABLE_000_A041
WHERE     Editeur is null

===== Variantes sur création et jointure ====

CREATE TABLE result AS
(SELECT first.*,
second.f1,
second.f2,
second.f3
FROM   first
INNER JOIN second
ON first.id = second.id);

CREATE TABLE events AS
(SELECT e010.*,
e110.summary
FROM   e010
INNER JOIN e110
ON e010.isn = e110.col1);

SELECT a.isn, a.nom
FROM e010duplicate a
INNER JOIN e010duplicate b ON a.nom = b.nom
WHERE a.isn <> b.isn

============= VALEURS NULL =========================
SELECT *
FROM `jarid`
WHERE A0302 IS NOT NULL

Injections Autorités par occurrence
UPDATE jarid j, A030 t
SET j.a0302 = t.A030
WHERE j.notice_id = t.notice_ref and t.occ = 2

==============================================

Restore a mysql database under ubuntu :

Type in console

$ mysql -h adresse_ip -u user -p -D nom_de_la_base_de_données < nom_du_fichier_sql

For example

$ mysql -h localhost -u root -ap -D mesvideos < mesvideos.sql

Backup one mysql Database under ubuntu :
Type in console

$ mysqldump -h adresse_ip -u user -p nom_de_la_base_de_donnees > nom_du_fichier_sql

For example

$ mysqldump -h localhost -u root -p mesvideos > mesvideos.sql

====================================================================

MySQL Fetch using PHP

PHP provides huge list of MySQL functions to access database from front end. Among those functions, we are going to discuss about some of them that are used to fetch data from database. Such functions are differed with the type of results they are expected to return.

In PHP, MySQL fetch results can be obtained by the following functions. All of these functions will fetch only one row per function call. If required, we should call such functions with a loop for getting subsequent MySQL fetch results row by row.

  • mysql_fetch_row()
  • mysql_fetch_assoc()
  • mysql_fetch_array()
  • mysql_fetch_object()
  • mysql_fetch_lengths()
  • mysql_fetch_field()

mysql_fetch_row()

This function will fetch data about single row with which the row pointer is currently exist. After fetching the entire row details, it will be returned as an array with number indices corresponding to the MySQL field offset.

For getting such resultant array, mysql_fetch_row() requires a resource data that is returned by executing query appropriate with MySQL fetch operations. If no results found for the query, then mysql_fetch_row() will return nothing as expected.

Let us consider the Users table, which we had taken for example while seeing about MySQL Left JOIN and MySQL Right JOIN. And the table data and structure is as follows.

mysql_user_table

The following PHP program is for fetching MySQL data using mysql_fetch_row(). Before that, the database connections are made with first two lines as usual.

$conn = mysql_connect(“localhost”,”root”,””);
mysql_select_db(“phppot_examples”,$conn);
$query = “SELECT * from Users”;
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_row($result);
print ”

";
print_r($row);
print "
";

This program will return values of the entire first row, since the row pointer is at the beginning. So the output will be as shown below that can be cross checked with the table data shown already.

Array
(
[0] => 1
[1] => admin
[2] => admin123
[3] => student
)

If we want to get all the row information, we should do the same process recursively by iterating through a loop until end of record. For example,

while($row = mysql_fetch_row($result)) {
print ”

";
print_r($row);
print "
";
}

mysql_fetch_assoc()

This functions is similar to the mysql_fetch_row(), except that, it will return array of row information containing column values are indexed with column name. So the result type is of an associative array where each column name and values of a single row is associated together as name,value pairs.

Let us replace mysql_fetch_row() with mysql_fetch_assoc() in the above program which will return the following array.

Array
(
[user_id] => 1
[user_name] => admin
[password] => admin123
[user_type] => student
)

By using this type of MySQL fetch, we can access the data by its name instead of its offset. Since, remembering the order of fields is too tough for huge list of columns, accessing by field name is easy and there by this function could be preferred in such scenario.

mysql_fetch_array()

This MySQL fetch method returns resultant array with both indices, that is, field offset and field name. So, it would be used most probably by having both option of indexing.

Unlike above two functions, mysql_fetch_array() accepts an optional argument for specifying resultant array index type and its possible values are,

  • MYSQL_BOTH – It is the default value that would be taken if no second argument is provided for this function. It will provide resultant array with both indices.
  • MYSQL_NUM – With this option, mysql_fetch_array() will return array with offset indices as same as mysql_fetch_row().
  • MYSQL_ASSOC – With this option, mysql_fetch_array() will return array with name indices as same as mysql_fetch_assoc().

By replacing mysql_fetch_row() with mysql_fetch_array(), the output array will be,

Array
(
[0] => 1
[user_id] => 1
[1] => admin
[user_name] => admin
[2] => admin123
[password] => admin123
[3] => student
[user_type] => student
)

mysql_fetch_object()

mysql_fetch_object() function will return MySQL data with same structure as returned by mysql_fetch_assoc(), but its type is different. mysql_fetch_object() returns object where as mysql_fetch_assoc() returns array. So, the way of accessing these data will also be differed. For example, if we are required to access user_name, after array fetch, it will be done by,

echo $row[“user_name”];

Or else after object fetch, it would be,

echo $row->user_name;

mysql_fetch_lengths()

This PHP function is used to return the string length each column value of the recently fetched row. So, before calculating the string length, any one of the above MySQL fetch functions need to be invoked.

For example, the following program is to fetch single row data using mysql_fetch_object() and to print the corresponding length array to the browser.

$conn = mysql_connect(“localhost”,”root”,””);
mysql_select_db(“phppot_examples”,$conn);
$query = “SELECT * from Users”;
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_object($result);
$student_length = mysql_fetch_lengths($result);
print ”

";
print_r($row);
print_r($student_length);
print "
";

And the output is,

stdClass Object
(
[user_id] => 1
[user_name] => admin
[password] => admin123
[user_type] => student
)
Array
(
[0] => 1
[1] => 5
[2] => 8
[3] => 7
)

Obviously, the object properties array and the length array are returned as shown above that could be cross checked manually to ensure the property value length is correct as expected.

mysql_fetch_field()

Unlike above functions, mysql_fetch_field() is for getting MySQL Database table’s field information instead of record data. And, this function also fetch one field per call and need loop implementation for getting more fields. These information array will be returned as an object which includes properties like, table name, field name, field maximum length, primary key flag offset and etc. For example, the user_id field details of Users table is returned as follows.

stdClass Object
(
[name] => user_id
[table] => Users
[def] =>
[max_length] => 1
[not_null] => 1
[primary_key] => 1
[multiple_key] => 0
[unique_key] => 0
[numeric] => 1
[blob] => 0
[type] => int
[unsigned] => 0
[zerofill] => 0
)

Note:mysql extenstion we have seen here are deprecated as of PHP version 5.5. So the alternative extension mysqli is used instead for later version of PHP. So for later version, the above functions should be changed by replacing its prefix mysql as mysqli

Example #1 mysql_num_rows() example

Retrieves the number of rows from a result set. This command is only valid for statements like SELECT or SHOW that return an actual result set.

<?php$link = mysql_connect(“localhost”, “mysql_user”, “mysql_password”);
mysql_select_db(“database”, $link);$result = mysql_query(“SELECT * FROM table1”, $link);
$num_rows = mysql_num_rows($result);echo “$num_rows Rows\n”;?>Different type of fetching function is given below:
1.    MYSQL_FETCH_ROW()
2.    MYSQL_FETCH_ARRAY()
3.    MYSQL_FETCH_ASSOC()
4.    MYSQL_FETCH_OBJECT()

1.    MYSQL_FETCH_ROW():  It is used to fetch data in numeric array. It gives result row as a numeric array.

$result=mysql_query(“select * from user_table”);
$result_row=mysql_fetch_row($result);
echo $result_row[0];
echo $result_row[1];
echo $result_row[2];

?>
2.    MYSQL_FETCH_ARRAY(): It is used to fetch data in numeric array as well as associative array . It gives result row as a numeric array as well as associative array.
$result=mysql_query(“select * from user_table”);
$result_row=mysql_fetch_row($result);
echo $result_row[0];
echo $result_row[1];
echo $result_row[2];
/* numeric array as well as associative array */
echo $result_row[id];
echo $result_row[first_name];
echo $result_row[last_name];

?>

3.    MYSQL_FETCH_ASSOC(): It is used to fetch data in associative array. It gives result row as a associative array.
$result=mysql_query(“select * from user_table”);
$result_row=mysql_fetch_row($result);
echo $result_row[id];
echo $result_row[first_name];
echo $result_row[last_name];

?>

4.    MYSQL_FETCH_OBJECT(): It is used to fetch data as object. It gives result row as a object.

$result=mysql_query(“select * from user_table”);
$result_row=mysql_fetch_row($result);
echo $result_row->id;
echo $result_row->first_name;
echo $result_row->last_name;

?>

extradrmtech

Since 30 years I work on Database Architecture and data migration protocols. I am also a consultant in Web content management solutions and medias protecting solutions. I am experienced web-developer with over 10 years developing PHP/MySQL, C#, VB.Net applications ranging from simple web sites to extensive web-based business applications. Besides my work, I like to work freelance only on some wordpress projects because it is relaxing and delightful CMS for me. When not working, I like to dance salsa and swing and to have fun with my little family.

You may also like...