Archive for the ‘mySQL’ Category
Setting up a database connection can be a difficult task if you google through different codes present online.
Here is the easy most method to create a dbconnection for PHP and mysql.
<?php
/* Online settings
$db = mysql_connect(”localhost”, “root”, “password”);
mysql_select_db(”dbname”,$db);
$siteurl = http://javedkhalil.com/;
*//* Local settings */
$siteurl = http://javedkhalil.com;
$db = mysql_connect(”localhost”, “root”, “”);
mysql_select_db(”dbname”,$db);
?>
Always write code both for Online and Local usage. Comment the code which is not being used. Also use siteurl variable and store website root path in it. It comes very very handy in times. We have to use many times website name inside the pages.
Now you can save this code in a file named like dbcon.php or whatever you like and include this file in php pages where you want to use dbconnection by this.
<?php Include (”dbcon.php”); ?> Here you are done.Another very simple code for dbconnection if you like, I am writing it here.
<?
$con=mysql_connect(”localhost”,”root”,”")or die(”Unable to connect”);
mysql_select_db(”dbname”,$con)or die(”Unable to open database”);
?>
This easy way to connect from mysql database through PHP will give a message if the connetion is not established.
Ever encountered with this WordPress MySQL error? MySQL Server Has Gone Away. And then your blog sidebars will refuse to show posts links, tags, recent posts, recent comments etc. I got this problem over and over again and I thought that I was using heavy plugins and MySQL query was timed out being very long. I searched for some solution but just found this answer that if I will use long query, MySQL server will be timed out and will be gone away. Funny!!! But I was hesitating to use more plugins and actually using limited options and other limits in existing WordPress plugins in my blog.
After a bit googling today I found a final solution for ‘MySQL server has gone away’ problem with my blog. There is a fix of wp-includes/wp-db.php file for this. Different versions of WordPress has different fix file. I am using WP 2.3 so I will give link here for WordPress 2.3 version.
How to Fix MySQL error
To install the fix, download the “fixed” wp-db…phptxt file, rename it to wp-db.php, and copy it to wp-includes/wp-db.php (overwriting the old file). You may want to backup the old file instead of overwriting it. You’ll need to do this each time you upgrade Wordpress, with the appropriate version of wp-db.php for that version of Wordpress.
To download fix file please follow origional source link.
Origional complete article link with downloadable fixes for all WordPress versions
I have successfully installed the fix and now my blog is problem free. Thanks to Rob’s Notebook for sorting this problem out.
Did you wonder sometimes that why mysql update command is not updating my records when the syntax of update query is perfectly alright?
This is the problem i stuck with couple of times and I just wondered and looked for solution but seriously never found any solution to that anywhere untill I found the answer to this question and I was relieved than from tension.
The problem was that I was naming the variables used in mysql query which were used by mysql itself.
Suppose that if there are seven variables in total number and mistakenly you named just one of them which is used by mysql, even than mysql update query will not work also and will not update records in the database.
For example by, from, status etc.
Insert mysql query will run and add records in database but update query will not be running.
Incorrect Query
UPDATE expenditures set by=’$by’, amount=’$amount’, from=’$from’, purpose=’$purpose’ where id=’$action’;
Correct Query
UPDATE expenditures set bby=’$bby’, amount=’$amount’, ffrom=’$ffrom’, purpose=’$purpose’ where id=’$action’;
Testing Your Query
The trick is to echo the query which you are running and test it in phpMyAdmin query window. i.e.
$myquery=”UPDATE expenditures set bby=’$bby’, amount=’$amount’, ffrom=’$ffrom’, purpose=’$purpose’ where id=’$action’”;
echo “$myquery”;
By echoing that you will get something like this
UPDATE expenditures set bby=’javed’, amount=’2000′, ffrom=’home’, purpose=’books’ where id=’2′
Finding Error
This is just an example. Alright now copy that query (echo result) and go to phpMyAdmin and open that database and i suggest that table also which is going to be updated and click query button. In query window write that result and try running that.
If its right it will update successfully but if that’s not right than it will give some error saying something like;
syntax error
‘You have some error near UPDATE expenditures set by=’$by’, amount=’$amount’, from=’$from’, purpose=’$purpose’ … in line 1‘
If it gives error than it will highlight those variables also which are not right and there you can guess that what’s wrong.
Always when your query is not running and giving error it is recommended that you echo that query and run that in phpMyAdmin query window and see cautiously the error message. Hope it will help.
Did you find any problem like this or query related? Please share with me.
See this table. We name it student.
| id |
name |
class |
marks |
| 1 |
javed |
four |
76 |
| 2 |
aamir |
three |
34 |
| 3 |
aijaz |
two |
77 |
| 4 |
sohail |
four |
33 |
Update command in SQL is used to change any record in the table. Records are to be manipulated or updated using update command. Conditional update is the most common type of update command used in MySQL also. You are requested to go through the SQL WHERE command before using update command as both are to be used in proper combinations. WHERE will tell the command that which record will be updated and where in row. Here is one simple command. Without using any WHERE command (or without using any restriction) the command will change all the records of the table. So let us start with the simple command. Our table name is student here.
UPDATE student SET class=’Five’
This command will change all the records of the table student and will change all the class fields to Five. This is not what is required in common cases so we will be changing records based on some conditions. Now we will change all the class four students to class nine. Our command should selectively update those records for which class is equal to “four” and will update them to “nine”. We will use one where clause along with update command for updating the records.
UPDATE student SET class=’nine’ WHERE class=’Four’
This command will update only those records for which class is equal to ‘Four’. So this way we can update records selectively. Now let us move one more step and change the records selectively based on some value in some other field also. We will change records for which class is equal to ‘Four’ and mark is more than 70. We will promote those students only who has got more than or equal to 70 mark.
UPDATE student SET class=’nine’ WHERE class=’Four’ and mark >= 70
This will only change the records for which class=four and mark is more than or equal to 70. We have added two conditions by using AND as a logical operator. This way we can continue with adding more AND combinations to the query. Depending on the logic requirement OR combination can be added to the WHERE clause to UPDATE the records.
UPDATE student SET class=’nine’ WHERE class=’Four’ and mark >= 70 and name=’javed’
or
UPDATE student SET class=’nine’ WHERE class=’Four’ and mark >= 70 and name like ‘%javed%’
WHERE command is used to collect the records from the table based on some condition specified by the where clause. More than one condition can be added to the where clause by using various logical expressions like AND, OR, < ( less than ), >(greater than) etc. Logical expressions plays important role in returning the desired records. Let us start with some examples. We are interested in the marks of Fourth class students. You can just follow the table creation process and the select query section discussed here. We will use the table named as student to work with WHERE clause.
Here is the table.
| id |
name |
class |
marks |
| 1 |
javed |
four |
76 |
| 2 |
aamir |
three |
34 |
| 3 |
aijaz |
two |
77 |
| 4 |
sohail |
four |
33 |
SELECT * from student where class=’Four’
Here is the result.
| id |
name |
class |
marks |
| 1 |
javed |
four |
76 |
| 4 |
sohail |
four |
33 |
This will return all the records from the table of class Four. This is what we require to get all the records of fourth standard students.
Other possible WHERE queries can be like these:
SELECT * FROM student where class = ‘Four’ and mark >77
SELECT * FROM `student` WHERE mark between 33 and 76
SELECT * FROM `student` WHERE name like ‘%sohail%’
Like is special treatment. It can be used as following.
SELECT * FROM `student` WHERE name like ‘%sohail’
SELECT * FROM `student` WHERE name like ’sohail%’
SELECT * FROM `student` WHERE name like ‘%sohail%’
%sohail will also fetch record like Najam Sohail
sohail% will also fetch record like Sohail Nadeem
%sohail% will also fetch record like abcd Sohail xyz
While = will see for exact matching value.
