SitePoint Sponsor |
|
User Tag List
Results 1 to 17 of 17
Thread: Select --- Where Xxx=''
-
Jun 19, 2001, 02:33 #1
- Join Date
- Jul 2000
- Location
- Singapore
- Posts
- 2,103
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Select --- Where Xxx=''
Hi,
Yeah. Weird Title.
Alright..
Just need to ask something about mysql error routines.
If when doing a query like:
$results = mysql_query("SELECT * FROM tablename WHERE item='$value'");
What if mysql can't find the item='$value' ?
Will the follow-up codes be executed?"Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world."
-- Albert Einstein
-
Jun 19, 2001, 02:37 #2
- Join Date
- Mar 2001
- Location
- Medina, OH
- Posts
- 440
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Yes and no. It will return an empty set and not produce an error and PHP will attempt to continue executing your script, but your script will not get far with no data to work with.
To get around this, you need to check if it's an empty set before processing it with your code. I don't have an example off the top of my head but you could probably find something in this forum about it.Kevin
-
Jun 19, 2001, 03:26 #3
- Join Date
- Jul 2000
- Location
- Singapore
- Posts
- 2,103
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Okay. Thanks
There's no mysql_error or something to report?"Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world."
-- Albert Einstein
-
Jun 19, 2001, 03:53 #4
- Join Date
- Jun 2001
- Location
- Georgia
- Posts
- 102
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
PHP Code:
$results = mysql_query("SELECT * FROM tablename WHERE item='$value'");
if ($result != ""){
//Do stuff here...
}else{
echo "No items found containing $value";
}
PHP Code:
$results = mysql_query("SELECT * FROM tablename WHERE item='$value'");
if ( !isset($result) ){
//Do stuff here...
}else{
echo "No items found containing $value";
}
--Odd
"We all live in a yellow subroutine."
"Some call it insanity; I call it inspiration!"
-
Jun 19, 2001, 04:02 #5
- Join Date
- Jul 2000
- Location
- Singapore
- Posts
- 2,103
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
COOL! thanks! that did it!
"Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world."
-- Albert Einstein
-
Jun 19, 2001, 04:10 #6
- Join Date
- Jul 2000
- Location
- Singapore
- Posts
- 2,103
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
ARGH! It didn't! =(
Her'es my code
PHP Code:
$results = mysql_query("SELECT * FROM tablename WHERE filename='$add'");
if ($results != '') {
$comments = htmlspecialchars("$comments", ENT_QUOTES);
$name = htmlspecialchars("$name", ENT_QUOTES);
mysql_query("INSERT INTO tablename VALUES ( '$add', '$datetime', '$name','$comments','$REMOTE_ADDR')");
echo '<tr><td>Your Comment has Been Added</td></tr></table></div><br><a href="comments.php?id='.$add.'">Back</a>';
}
else {
echo '<tr><td>Error Encountered : File Entry does not Exist.</td></tr></table></div><br><a href="javascript:history.go(-1)">Back</a>';
exit;
}
"Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world."
-- Albert Einstein
-
Jun 19, 2001, 07:34 #7
- Join Date
- Jun 2000
- Location
- Sydney, Australia
- Posts
- 3,798
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
From the manual http://www.php.net/manual/en/function.mysql-query.php
mysql_query() returns TRUE (non-zero) or FALSE to indicate whether or not the query succeeded. A return value of TRUE means that the query was legal and could be executed by the server. It does not indicate anything about the number of rows affected or returned. It is perfectly possible for a query to succeed but affect no rows or return no rows.No one can remember exaclty how every function works and I am continually discovering new functions as I takle new tasks.
As the manual says, the function mysql_query() will return 0 (which evaluates to false as does an empty string) only if the sql query was illegal or there was an error in communicating with the database. (no connection in place etc). An empty set is a valid set! The set of all records that match your criteria can be no records - that is valid.
The function mysql_num_rows($results) will tell you how many rows are in the result set.
-
Jun 19, 2001, 07:58 #8
- Join Date
- Jul 2000
- Location
- Singapore
- Posts
- 2,103
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thanks Freakysid!
Guess what? The php Manual is better for gathering information then from the mysql documentation.
I was searching hard at the documentation at www.mysql.com
anyways, the workaround is using mysql_num_rows
If its 0 == error.
great!!! phew.
just a moment..
...from one of the comments..it read = '
Watch out when you make queries based on variables from a surfer. SELECT * FROM table where ID=$ID can easily be hacked to empty your table. Parse all variables submitted through forms/get if they are to be used in any similar situation. Of course, the hacker would need to know the name of your table."Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world."
-- Albert Einstein
-
Jun 19, 2001, 08:37 #9
- Join Date
- Dec 2000
- Location
- Idaho, USA
- Posts
- 452
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
What that quote is talking about may be true, but unless you code really loosely and have extremely sloppy queries I don't forsee that as a problem.
As for the first question/post. Here is an example:
PHP Code:$sql = "SELECT * FROM table WHERE column='$var' AND date > '$date'";
$result = mysql_query($sql);
if(mysql_num_rows($result) < 1){
echo("No results.")
}else{
while($row = mysql_fetch_array($result)){
extract($row);
echo("Name: $name<br>Phone: $phone<br>Date: $date");
}
}
God BlessBlamestorming: Sitting around in a group discussing why a deadline was missed or a project failed and who was responsible.
Exbabylon- Professional Internet Services
-
Jun 19, 2001, 08:43 #10
- Join Date
- Jul 2000
- Location
- Singapore
- Posts
- 2,103
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally posted by exbabylon
What that quote is talking about may be true, but unless you code really loosely and have extremely sloppy queries I don't forsee that as a problem.
I might just have sloppy coding which will lead to these problems.
Originally posted by exbabylon
As for the first question/post. Here is an example:
PHP Code:$sql = "SELECT * FROM table WHERE column='$var' AND date > '$date'";
$result = mysql_query($sql);
if(mysql_num_rows($result) < 1){
echo("No results.")
}else{
while($row = mysql_fetch_array($result)){
extract($row);
echo("Name: $name<br>Phone: $phone<br>Date: $date");
}
}
God Bless
if i only want 4 out of 8 variables, should i use extract too?
Thanks"Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world."
-- Albert Einstein
-
Jun 19, 2001, 09:12 #11
- Join Date
- Dec 2000
- Location
- Idaho, USA
- Posts
- 452
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
You can use extract() to pull all of the key's of an array into variables with thier values being their values (Ambigious, no?). However, it can create problems when you are dealing with a lot of variables and your column name may be the same as some already existing variables.
Take a good look at this URL:
http://php.net/manual/en/function.extract.php
As for sloppy queries I may need to just leave that as sloppy coding. For example, if I have a link like so:
Code:<a href="display.php?query="SELECT+name+FROM+table_name">View All Entries</a>
That is a really far fetched cenareo, but I have seen a lot worse here in this board.
Have fun, and God Bless,
Alex StantonBlamestorming: Sitting around in a group discussing why a deadline was missed or a project failed and who was responsible.
Exbabylon- Professional Internet Services
-
Jun 19, 2001, 15:24 #12
- Join Date
- Jun 2000
- Location
- Sydney, Australia
- Posts
- 3,798
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I remember that we had a conversation about this a month or two ago (security of queries using user supplied data). One thing that PHP/MySQL will not allow is for multiple SQL queries to be sent using mysql_query(). I don't know who is responsible for this (whether it is PHP or MySQL) because, I can certainly execute multiple queries in the MySQL command line client. However, I remember it being discussed that some other database & scripting platforms (ahem, I think it was the Microsoft ASP/SQL Server combo) would allow this. For example I just wrote the following test program:
PHP Code:<?php
$value = '1; drop table TestTable;';
$dbcon = mysql_connect("localhost", "xxxxxx", "xxxxxx");
mysql_select_db("neaturl", $dbcon);
$sql = "INSERT INTO TestTable SET value = $value";
echo "<br> $sql";
$result = mysql_query($sql);
$result = (int)$result;
echo "<br>$result";
?>
Code:INSERT INTO TestTable SET value = 1; drop table TestTable; 0
Oh yea, I just read freddydoesphp's post in another thread and it reminded me of a something. You can set up users which only have certain permissions to perform certain actions on certain databases, tables and even columns! So you can, if you want to, really tighten up what sort of access to the database the user your script is connecting to mysql as has.Last edited by freakysid; Jun 19, 2001 at 16:42.
-
Jun 20, 2001, 03:41 #13
- Join Date
- Jul 2000
- Location
- Singapore
- Posts
- 2,103
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
So my code is alright I presume.
Since the value I'm gathering from the form is only used in the WHERE item='$item'
BTW..how do you go about creating a new user?
I don't think its possible with HostRocket?
Thank You guys!"Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world."
-- Albert Einstein
-
Jun 20, 2001, 03:48 #14
- Join Date
- Mar 2001
- Location
- Medina, OH
- Posts
- 440
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
IIRC HR uses Cpanel which means you go to Advanced > SQL Database > and find the section titled "users" and fill in the username/password box to create a new user. Then to assign that user to a database, find, under the list of your databases, the thing that looks like
User: <drop-down> Db: <drop-down> Add User to Db
and select the user you created in the user drop-down and the DB to assign it to in the DB drop down and then click the button.Kevin
-
Jun 20, 2001, 05:02 #15
- Join Date
- Jul 2000
- Location
- Singapore
- Posts
- 2,103
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally posted by tubedogg
IIRC HR uses Cpanel which means you go to Advanced > SQL Database > and find the section titled "users" and fill in the username/password box to create a new user. Then to assign that user to a database, find, under the list of your databases, the thing that looks like
User: <drop-down> Db: <drop-down> Add User to Db
and select the user you created in the user drop-down and the DB to assign it to in the DB drop down and then click the button."Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world."
-- Albert Einstein
-
Jun 20, 2001, 05:12 #16
- Join Date
- Mar 2001
- Location
- Medina, OH
- Posts
- 440
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I'm pretty sure that's not possible.
Kevin
-
Jun 20, 2001, 05:17 #17
- Join Date
- Jul 2000
- Location
- Singapore
- Posts
- 2,103
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally posted by tubedogg
I'm pretty sure that's not possible."Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world."
-- Albert Einstein
Bookmarks