Once a column is set to Auto Increment the value would Increment by one for every record added. Therefore, the column value for each record would be unique and hence it could be set as a primary key. There may be times when you would want it to Increment from a specific value. This is how it can be done-
ALTER TABLE table_name AUTO_INCREMENT=1001;
In the above example, Auto Increment would begin from 1001 rather than 0.
This is an error that one faces when they use mysql and mysqli Interchangeably. Functions that begin with “mysql” are deprecated and no longer supported in the latest versions of PHP. So, one should either use mysql throughout the program or mysqli, mix and match doesn’t work. Mix and match might work in the grocery store that might help you to get sale prices but unfortunately it doesn’t work here! 😀
When you are Involved in comparing and updating two different tables there may be times when you would need the last ID of a table so that it can be used in another table provided the ID is a primary key set to Auto Increment.
The way you would go about this is to use the max keyword to get the last ID of a table and then use the mysql_fetch_row function to fetch a row of data from the result handle. This value is then saved in a PHP Variable that can be later used for further manipulation. The overall code would appear to be something like this-
//Database Connection
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);
//Get the Last ID of a table. The column name is "SNo"
$last_id = mysqli_query($db,"SELECT max(SNo) FROM Test4_HarvestData");
if (!$last_id) {
   die('Could not query:' . mysql_error());
}
//The Last ID saved in a PHP Variable
$row_sno= mysqli_fetch_row($last_id);
echo "<br>The Last ID is : ".$row_sno[0];
There maybe times when you need to create a new table that uses the same structure as an existing table. I had this situation today, where I was working on a Test table in a database and then I needed to create a new table that would be used in production. And obviously the columns and primary key had to be the same. There are many ways where this could be achieved. One of the ways is to do it manually, which is fine until you are looking at a table that has 20 columns with constraints in almost every other column!
The simpler and faster way to replicate the structure of an existing table would be to use a SQL Query. And by a query I don’t mean a complicated, convoluted query! It is in fact very simple and straightforward.
This is the Query you need to use when you would want to create a new table that has the same structure of an existing table-
Create Table NewTable_Name Like OldTable_Name
Where NewTable_Name is the Name of the New Table
and
OldTable_Name is the name of the Old Table
MYSQL is one of the popular Relational Database Management Systems out there. One of the basic commands that you start out with is to see the list of tables in a database. The command for achieving this is
show tables;
Sometimes when you enter that command in the MYSQL console, you might see a message that looks something like this
The Error message states that no database is selected. The command would only work if we first select the database that we want to work on.
The command for selecting a database is-
use database_name;
where database_name is the Name of the Database.
Once you select the database, then the next step would be to execute the show tables command which would then show you the list of tables that exist within the database.
As you can see the command now ran successfully. And it shows that I have two tables in the database-Harvest_Users and Table2.
If you are planning to Import data to a MySQL Database, the first thing which need to check is the whether your data is in the right format. MySQL is best compatible with files in the CSV format. So, in case you have an Excel file you could save that file as a CSV File and you don’t need a special software to do that. All you would have to do is click on File from the Menu and select Save As and make sure the Format Type is CSV.
Now once you have that checked off your list the next and final step is Importing this data to the MySQL Database. If you google this you would get many different explanations and ways which would make you confused. It might ask you to write SQL Queries which you totally don’t have to. If you are using PhpMyAdmin, the easiest and fastest way to do it is to use the Import Function in the menu. Saves you time creating a table and columns corresponding to the data.
Make sure you select the database, then click on the Import button, choose the file and format and Import!
Ricky Raghavan 