Page 1 of 1
Database Sticky
Posted: Sun Oct 27, 2002 5:07 am
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
Posted: Sun Oct 27, 2002 5:21 am
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
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

mysql_fetch_...
maybe some good beginner tutorial links
(not only for mysql, but most questions in 'Database' are related to mysql)
Posted: Sun Oct 27, 2002 5:27 am
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
Posted: Sun Oct 27, 2002 5:30 am
by volka
ok, I shouldn't edit posts every time something new comes to mind.
from now on: something new -> new post

Posted: Sun Oct 27, 2002 5:37 am
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

Posted: Sun Oct 27, 2002 10:41 am
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
Posted: Sun Oct 27, 2002 11:47 am
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.
Posted: Sun Oct 27, 2002 12:36 pm
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();
Posted: Sun Oct 27, 2002 3:48 pm
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.
Posted: Wed Jul 23, 2003 2:51 pm
by nielsene
If we do this, maybe we should add an entry for PHP5 and MySQL and the whole unbundling thing
Posted: Wed Jul 23, 2003 11:21 pm
by phice
Add mysql_affected_rows(), so I can finally understand what it does. The easy way!

Posted: Thu Jul 24, 2003 1:54 am
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...)
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.'
