4 replies to this topic

[Caseyweederman]

So I've got this nebulous grasp on PHP and MySQL, which is more or less just enough to get me in deep enough that it's easier to plow through than climb back out.
There are a couple things I'm curious about, though.
And I know, I know that these days this kind of thing is less and less important as hardware gets better and better. I just want to do it right.

So which is more efficient? Which is faster?
Let's say I want to enter a table into the database.
Scenario a) Gather the data, run a query to see if that name has been taken. If not, enter the data. (TWO QUERIES)
Scenario B) Gather the data, go ahead and insert it, return an error if that name has been taken. (ONE QUERY, UNIQUE ROW)

There was another similar scenario too... I'll bring that in when I recall it.
Oh, I think I solved that one with a case is statement. Multiple updates in a single query.
Well, how about that? Which is more efficient? Which is faster?
An update query for each iteration of a thing, or a query with a number of updates equal to the number of iterations?

[Drackir]

Well, from a programming perspective, it's bad-form to control program flow using error messages. However, if you know for a fact that the error was caused by the fact that the user (or whatever) already exists, then it's fine to just use the output of the query to let them know whether it was a success. If, on the other hand, the query could fail for other reasons (such as bad DB connection) and you have no way of differentiating the issue, then you should definitely go with your first option. Typically, I would just go with the first option since it's clear and DB queries don't really take up much time (especially when the MySQL server is on the same box as the webserver).

With regards to your UPDATE question, the best thing there would be to iterate your loop and append each UPDATE to a string. Then, at the end, run all the UPDATEs with a single database call. Of course it will be slower to run the UPDATEs all individually and since multiple statements in one call is not a problem, this is the best way.

[Caseyweederman]

Sweet! Thanks.
I'm trying to figure out how to catch MySQL's error, but I'm getting a bunch of other errors instead.
Warning: mysql_errno(): supplied argument is not a valid MySQL-Link resource in create.php on line 38
I passed it $dbc, which carries the mysqli_connect bit.

Okay, that's what I figured with the UPDATE bit.

Thanks for the help!

Oh, how about this.
I read somewhere that it's good to close the database connection when you're done with it. Is it fine to just close it at the end of the page, or should I close it immediately after each query? I want to build up good habits, you know?

[Drackir]

So you have something like:

$dbc = mysqli_connect("stuff","and","things",":-)");
$result = mysqli_query($dbc, "SELECT Test FROM tblTest");

and that throws an error? Did you check if there was an error in the connection code?

Regarding closing the connection, if you closed it after each query, that would add a lot of unnecessary overhead. Not only would you have to run each query, but you'd have to authenticate yourself each time as
well. Closing it at the end of the file is fine. I think typically it's not necessary, but it's good practice.

Also good practice: use parameterized queries. It took me a long time before I found these and, man, have they made my life easier (especially in SQL Server). You can read up on how to use them here: http://www.php.net/m....bind-param.php

[Caseyweederman]

No, that doesn't throw the error, trying to do anything with mysqli_errno or its brother did.
Okay, I'll just close it up at the end.
Parameterized queries... I'll try to squish that into my brain.
Thanks!