Database Sticky

We know you have an opinion on how things should be run around here. These are suggestions for the forums, and the website.This forum is not a place to ask for suggestions to your own coding (or otherwise) problems.

Moderator: General Moderators

Post Reply
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Database Sticky

Post by twigletmac »

I'd like to put a sticky in the database forum to answer some of the more frequently asked questions. If you have any suggestions as to what should form part of it could you post them here.

nielsene already posted some ideas as to what should be included:
nielsene wrote:Discussion of re-using database connections between pages, what pconnect versus connect really is, why you can't just stick a resource in a session variable....
I've also got a few in mind - why does using UPDATE change every record and why do I get a Resource ID error amongst them.

Just wanted to see what you lot thought would be important.

Thanks,
Mac
Last edited by twigletmac on Mon Jan 06, 2003 5:28 am, edited 1 time in total.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

  • before asking questions about "not a valid ressource id" use
  • mysql_connect(...) or die(mysql_error());
  • mysql_select_db(...) or die mysql_error()
  • mysql_query($query ...) or die($query. ' :'. mysql_error())
or similar (i.e. pg_last_error)
same with all other error :D

if a query leads to an unexpected result try

Code: Select all

$query = ...
echo '<-- ', $query, '-->';
$result = mysql_query....
maybe the query-string is not what it is supposed to be. Even better error_log($query) since it points to the error-log-file, each scripter should know about it ;)

addslashes, mysql_escape_string vs. bogus user input

Code: Select all

$id = mysql_query('SELECT id from myTable');
echo $id
is wrong :arrow: mysql_fetch_...
maybe some good beginner tutorial links

(not only for mysql, but most questions in 'Database' are related to mysql)
Last edited by volka on Sun Oct 27, 2002 5:32 am, edited 3 times in total.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Definitely - a debugging example using those was going to be at the top - especially since most books seem to favour the,

Code: Select all

mysql_query("SELECT * FROM table") or die('Query not working.');
approach.

Mac
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

ok, I shouldn't edit posts every time something new comes to mind.
from now on: something new -> new post ;)
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

maybe some links to existing threads that cover general topics.
  • trouble with UPDATE-query --> read this
  • trouble REPLACE-query --> read this
  • ...
and the unavoidable RTFM-entries :D
User avatar
hob_goblin
Forum Regular
Posts: 978
Joined: Sun Apr 28, 2002 9:53 pm
Contact:

Post by hob_goblin »

Database Tips/Tricks: (mysql)

1) Something messing up? Try using mysql_error();

Code: Select all

mysql_query("SELECT * FROM table") or die(mysql_error());
2) Should I close my connection using mysql_close? Well, it depends. If you are using mysql_pconnect(), YES! Persistant connections will go on for a good bit of time unless you close them when you are finished with them. The regular mysql_connect() doesn't need to be closed as PHP checks for open resource variables at script end and closes them.

3) Worried about being h4x0red? If you let people input information that will change the output of a query, make sure to do error checking. The manual has alot of information on this.

4) Do you get a 'this is an invalid resource' sometimes? Check your queries, cause one of them is probably returning 0 results. See #1 for some error checking.

5) Need a backup? Want to make tables easily? Look into phpMyAdmin
jason
Site Admin
Posts: 1767
Joined: Thu Apr 18, 2002 3:14 pm
Location: Montreal, CA
Contact:

Post by jason »

Of course, here is my tutorial on PHP and MySQL

http://beta.devnetwork.net/tutorials.php?id=3

I don't like the or die(mysql_error()) method. I would rather handle it myself. The "or die()" method doesn't allow you the chance to handle your error.
User avatar
hob_goblin
Forum Regular
Posts: 978
Joined: Sun Apr 28, 2002 9:53 pm
Contact:

Post by hob_goblin »

jason wrote: I don't like the or die(mysql_error()) method. I would rather handle it myself. The "or die()" method doesn't allow you the chance to handle your error.
I only use or die() on things like mysql_connect, i may put them on queries, but only for debugging, after all the bugs are fixed i strip out the or die()'s and do error checking with mysql_num_rows();
User avatar
phice
Moderator
Posts: 1416
Joined: Sat Apr 20, 2002 3:14 pm
Location: Dallas, TX
Contact:

Post by phice »

WRONG

Code: Select all

SELECT * FROM table WHERE col1 = email@email.com
CORRECT

Code: Select all

SELECT * FROM table WHERE `col1` = 'email@email.com'
Note: Use the tilda key, along with shift, around the column name. BUT, using the single-quote around the columns value.
Image Image
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

If we do this, maybe we should add an entry for PHP5 and MySQL and the whole unbundling thing
User avatar
phice
Moderator
Posts: 1416
Joined: Sat Apr 20, 2002 3:14 pm
Location: Dallas, TX
Contact:

Post by phice »

Add mysql_affected_rows(), so I can finally understand what it does. The easy way! :D
Image Image
qartis
Forum Contributor
Posts: 271
Joined: Sat Dec 14, 2002 4:43 pm
Location: BC, Canada
Contact:

Post by qartis »

Yes, `backticks` around feild and table names.

Explain how to debug..
You have an error in your SQL query near "WHERE `foo` = 'bar'"
mysql_function(): supplied argument is not a valid mysql result resource in filename

Explaining how to properly use connection handles and result resources (I've seen them tried in GET, POST, cookies, sessions, bookmarks...) :roll:


Explaining how mysql calls differ from others (PostgreSQL, MSSQL)

Sometimes, I wonder why mysql doesn't have one of these, opting instead to write a 1500 page 'manual.' :)
Post Reply