Creating a MYSQL query in a loop SEE LAST POST

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

reecec
Forum Contributor
Posts: 218
Joined: Sun Apr 02, 2006 7:12 am

Creating a MYSQL query in a loop SEE LAST POST

Post by reecec »

Hi all

Just another quick issue this time.]
Is it not possible to use mysql_fetch_row($result): when you have used a where clause in $result as i get the mysql_fetch_row(): error

sorry i dont know what its called where the function looks at the () var in the brackets

thanks reece
Last edited by reecec on Mon Aug 14, 2006 10:47 am, edited 2 times in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

code please.Image
reecec
Forum Contributor
Posts: 218
Joined: Sun Apr 02, 2006 7:12 am

Post by reecec »

sorry wasnt sure if you needed to see the code

Code: Select all

$table=$_REQUEST['table'];

$result = mysql_query("select * from $table");


$searchquery='mysql_query("SELECT * FROM test ';
$i=0;
while ($i < mysql_num_fields($result)) {
$meta = mysql_fetch_field($result, $i);
$name=$meta->name;
$con = ( $field[$i]=='' ) ? '"' : "$name = $field[$i]";
    $finish = ( $i == 1) ? '")' : ' AND ';
    $searchquery .= "$con$finish";
    $i++;
} 
 echo $searchquery;


if ($searchquery) {
    echo "Searched $table";
} else {
    echo "Error Searching $table";
}

echo '<table border="1" cellspacing="0" cellpadding="0">';

while ($field=mysql_fetch_field($result)) {

echo "<th>";
echo "$field->name";
echo "</th>";
}
echo "<th>";
echo "Delete";
echo "</th>";



while ($row = mysql_fetch_row($searchquery)) {
echo "<tr>";
for ($i=0; $i<mysql_num_fields($searchquery); $i++) {
echo "<td>";
echo "$row[$i]";
echo "</td>";
}}
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

In the code you've posted $searchquery is merely a string, not the result resource from MySQL. The output from your inner while loop will only be written out once with the current code. You may want to cache the results it creates. And finally, your code will generate invalid HTML.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

.. and is also vulnerable to sql injection.
reecec
Forum Contributor
Posts: 218
Joined: Sun Apr 02, 2006 7:12 am

Post by reecec »

Hi thanks all for you replyes

but when i check the query it works and doesnt give an error and says sucsess


but even if i do a standard on a new script
mysql_fetch_rows
it works but as soon as i tell it what row to get using a WHERE is doesnt like it can i not use this to get a specific row


thanks reece
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I have no clue what you just said.

Image
reecec
Forum Contributor
Posts: 218
Joined: Sun Apr 02, 2006 7:12 am

Post by reecec »

sorry an example may help


this is fine

Code: Select all

$result=mysql_query("SELECT * FROM anytable");
$field=mysql_fetch_field($result)
but when a where clause is added it gives an error

Code: Select all

$result=mysql_query("SELECT * FROM anytable WHERE field=anything");
$field=mysql_fetch_field($result)

thanks reece
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

  • Place backticks around database, table and field references.
  • Use quotes around strings.
  • Use database native types for numbers, dates and the like.
reecec
Forum Contributor
Posts: 218
Joined: Sun Apr 02, 2006 7:12 am

Post by reecec »

thanks thats sorted it


reece
reecec
Forum Contributor
Posts: 218
Joined: Sun Apr 02, 2006 7:12 am

Post by reecec »

Hi all


as you know im trying to do a SQL query this is what it echos

mysql_query("SELECT * FROM test WHERE username = 'test' AND profile = 'test' ")
what would be wrong with this as it wont fetch fields with this query

thanks for your help reece
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

try the mysql_error() function
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

You've forgotten the first bullet point I made already.
reecec
Forum Contributor
Posts: 218
Joined: Sun Apr 02, 2006 7:12 am

Post by reecec »

hi


i put them on the WHERE but not the db name so i added them like this
mysql_query("SELECT * FROM `test` WHERE 'username' = 'test' AND 'profile' = 'test' ")
but noting else changed is this what you mean

thanks reece
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

you are using ticks incorrectly in this query:
mysql_query("SELECT * FROM `test` WHERE 'username' = 'test' AND 'profile' = 'test' ")
instead, it should say this:

Code: Select all

$sql = mysql_query("SELECT * FROM `test` WHERE username = 'test' AND profile = 'test' ");
or even

Code: Select all

$sql = mysql_query("SELECT * FROM `test` WHERE `username` = 'test' AND `profile` = 'test' ");
Post Reply