PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Sun Jul 21, 2019 4:38 am

All times are UTC - 5 hours




Post new topic This topic is locked, you cannot edit posts or make further replies.  [ 29 posts ]  Go to page 1, 2  Next
Author Message
PostPosted: Thu Jan 22, 2004 5:09 pm 
Offline
DevNet Resident
User avatar

Joined: Thu Oct 16, 2003 5:49 pm
Posts: 1972
Location: Manchester (UK)
Just the following 3 'tips' can pretty much resolve all common MySQL problems. Unless you've got at least these 3 things then it's not really worth asking for help (imho), as these are the things you'll probably get told to do first ;)

Error Reporting
Put error_reporting(E_ALL); at the top of your script. This will highlight any warnings/notices that maybe affecting your code.
E.g.
Syntax: [ Download ] [ Hide ]
 
<?php
error_reporting(E_ALL);
 


Use mysql_error()
99% of MySQL problems can be resolved using mysql_error(). If queries are failing, it will tell you why.
E.g.
Syntax: [ Download ] [ Hide ]
 
$db = mysql_connect('localhost', 'user', 'password') or die(mysql_error());
mysql_select_db('thedb') or die(mysql_error());
$sql = "SELECT * FROM foo";
$result = mysql_query($sql) or die(mysql_error());
 


Put queries in variables
Putting the queries into a variable enables you to echo out the var to check the query is what you expect it to be.
E.g.
Syntax: [ Download ] [ Hide ]
 
$sql = "SELECT name, email FROM user WHERE id=$id";
echo $sql; //this will show you if $id is set or not, and what it's value is
 


Top
 Profile  
 
 Post subject: hack possible
PostPosted: Thu Jan 22, 2004 8:33 pm 
Offline
Forum Contributor

Joined: Wed Jan 14, 2004 11:21 am
Posts: 313
Location: planet earth


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jan 22, 2004 8:36 pm 
Offline
DevNet Resident
User avatar

Joined: Thu Oct 16, 2003 5:49 pm
Posts: 1972
Location: Manchester (UK)


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jan 23, 2004 5:57 am 
Offline
Forum Contributor

Joined: Wed Jan 14, 2004 11:21 am
Posts: 313
Location: planet earth
why are field names or variables named "id" always IDs as numbers? They're not always. And it's better and more secure to quote any data passed to mysql, also IDs.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jan 23, 2004 6:11 am 
Offline
Her Royal Site Adminness
User avatar

Joined: Tue Apr 23, 2002 2:21 am
Posts: 5371
Location: Essex, UK
It's better and more secure to validate all input before you get anywhere near the SQL statement - ensure that what you expect to be a number is, that there is no SQL injection attempt - and then write your SQL statement accordingly. Then you can quote the strings and leave the INT's unquoted and not be anymore secure or insecure than if you had quoted the lot plus you'll be sending MySQL the data type it expects.

Mac


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jan 23, 2004 8:22 am 
Offline
Forum Newbie
User avatar

Joined: Tue Jul 08, 2003 6:13 am
Posts: 16
Location: UK
These days I always use this bit of code for generating ids rather than relying on numbers;

Syntax: [ Download ] [ Hide ]
<?php
 
$unique_id = md5(uniqid(rand(),1));
 
$token_result = mysql_query("SELECT id FROM table WHERE id='$unique_id'",$db);
 
while($num_rows = mysql_num_rows($token_result)){
                       
    $unique_id = md5(uniqid(rand(),1)); 
                       
}
 
 
?>


Then you just use $unique_id when you insert the data into your database. You get a value of something like 'c6127375320d85153ba1b334ab18330a'.


Top
 Profile  
 
 Post subject: i love forums
PostPosted: Fri Jan 23, 2004 8:41 am 
Offline
Forum Contributor

Joined: Wed Jan 14, 2004 11:21 am
Posts: 313
Location: planet earth
thats why i like forums that much :)
talking around a topic with no real end and total confusion of the one who opened the topic ...
(new topic for that hypothese?)


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jan 23, 2004 8:42 am 
Offline
Her Royal Site Adminness
User avatar

Joined: Tue Apr 23, 2002 2:21 am
Posts: 5371
Location: Essex, UK
I tend to prefer an auto-incremented number 'cause it makes it easier to store it as a foreign key in another table.

Mac


Top
 Profile  
 
 Post subject:
PostPosted: Sat Jan 24, 2004 2:16 am 
Offline
DevNet Master

Joined: Thu Jan 30, 2003 9:26 pm
Posts: 2893
Location: Glasgow, Scotland
Good first post. Just one thing to add: sticking a __FILE__ and __LINE__ somewhere in the die() output can be useful. If there are many queries in the script you can quickly find the code causing the problem.


Top
 Profile  
 
 Post subject:
PostPosted: Sat Jan 24, 2004 2:42 am 
Offline
DevNet Resident
User avatar

Joined: Thu Oct 16, 2003 5:49 pm
Posts: 1972
Location: Manchester (UK)


Top
 Profile  
 
 Post subject:
PostPosted: Mon Feb 09, 2004 2:49 pm 
Offline
Forum Contributor
User avatar

Joined: Tue May 20, 2003 4:49 pm
Posts: 400
this works as well as seperating the variable from the query function:
Syntax: [ Download ] [ Hide ]
<?php
mysql_query($query = "SELECT * FROM table;");
print"$query";
?>


Top
 Profile  
 
 Post subject:
PostPosted: Sun Feb 15, 2004 3:09 am 
Offline
Forum Commoner
User avatar

Joined: Wed Feb 04, 2004 1:26 am
Posts: 87


Top
 Profile  
 
 Post subject:
PostPosted: Sun Feb 15, 2004 1:19 pm 
Offline
DevNet Master

Joined: Thu Jan 30, 2003 9:26 pm
Posts: 2893
Location: Glasgow, Scotland


Top
 Profile  
 
 Post subject:
PostPosted: Sun Feb 15, 2004 1:35 pm 
Offline
Site Admin

Joined: Thu Apr 18, 2002 3:14 pm
Posts: 1767
Location: Montreal, CA


Top
 Profile  
 
 Post subject:
PostPosted: Tue Mar 09, 2004 11:28 am 
Offline
DevNet Resident
User avatar

Joined: Wed Sep 25, 2002 7:47 pm
Posts: 1708
just thought i'd throw a little bit into this one... another thing to help you along is using PHPMyADMIN..

yes, it does do everything for you, but at the same time it shows you the queries it uses to do all of it's operations. if not anything else, it will help you be more independant for structurizing. i see all the time where people ask how to do a simple query, but fail to see how phpmyadmin does it for a basic idea.

debugging is used this way in that it can verify queries to make sure they work. so, instead of putting it straight into production, try using the query in phpmyadmin and it's nice interface to see where you are going wrong.

btw, excellent writeup mark ;)


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic This topic is locked, you cannot edit posts or make further replies.  [ 29 posts ]  Go to page 1, 2  Next

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 6 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group