SELECT query problems

[corruption]

here's the code

<?php 
include 'conn.php';  

//make variables  

$fname = $_POST['fname'];    

//strip slases  

$fname = stripslashes($fname);    

$sql = "SELECT * FROM test ORDER BY 'fname' ASC LIMIT 0, 15";  $result = mysql_query($sql);   

if(!$result) 
{ echo mysql_error();   

} 
else {   

echo "Success...";   

}  

echo ($result);  

?>  

here's the result
Success...Resource id #4

what i'm trying to do:

Simply select all of the data from the table order by fname field asc with 15 on a page.

[yelvington]

The result of mysql_query is a query resource that has to be further processed by one of the mysql_fetch_* functions. If you try to echo it directly, you'll get the error message you cited.

[helz]

for most newbie beginings this has been the method used:

$i=1;
while ($row = mysql_fetch_array($result)) {//creates loop and loads each row of the table into the array called: $row
   $field1 = $row['field1'];//set the value of each field into a variable
   $field2 = $row['field2'];
   $field3 = $row['field3'];
   echo "Row $i:\nField 1 = $field1\nField 2 = $field2\nField 3 = $field3\n\n";
   $i++;
}

there are lots of variations of this method, not sure on which is 'better' or faster but depends on what you need.

mysql_fetch_array -- Fetch a result row as an associative array, a numeric array, or both.
mysql_fetch_assoc -- Fetch a result row as an associative array
mysql_fetch_field -- Get column information from a result and return as an object
mysql_fetch_lengths -- Get the length of each output in a result
mysql_fetch_object -- Fetch a result row as an object
mysql_fetch_row -- Get a result row as an enumerated array

[LordShryku]

Remove the single quotes around fname also. MySql may allow this, but it's not standard SQL.

[helz]

ah, i tend to leave all that junk out and go the lazy route with:
extract($row);
🙂

[LordShryku]

I meant in the SQL query 🙂
$sql = "SELECT * FROM test ORDER BY -->'fname'<-- ASC LIMIT 0, 15";

[corruption]

what is the $i=1 for?

[corruption]

new updated...

<?php  

include 'conn.php';  

/*make variables  

$fname = $_POST['fname'];    

//strip slases  

$fname = stripslashes($fname);  */  

$sql = "SELECT * FROM `test` ORDER BY fname ASC LIMIT 0 , 15"  $result = mysql_query($sql);  

$result_array = mysql_fetch_row($result);  

foreach($result_array as $data) {  	
print $data;  }  

/*  if(!$result) { echo mysql_error();  

 } 
else {   

echo "Success...";   

}  

echo ($result);*/  

?> 

For starters i'd just like to be able to see the data in a plain table of output, then i'll get into specifics of how i want to format the tables and what else i might include that isn't in the database at the moment

[helz]

change:

$sql = "SELECT * FROM `test` ORDER BY fname ASC LIMIT 0 , 15"  $result = mysql_query($sql);

to:

$sql = "SELECT * FROM test ORDER BY fname ASC LIMIT 0 , 15";
$result = mysql_query($sql);

the $i was just in the example to output:
Row 1:
Field 1 = blah
Field 2 = blah
Field 3 = blah

Row 2:
Field = blah
...

[corruption]

so it seems as if i should have something like:

<?php
include 'conn.php';

$sql = "SELECT * FROM `test` ORDER BY fname ASC LIMIT 0 , 15";
$result = mysql_query($sql);
$i=1; 
while ($row = mysql_fetch_row($result)) {
//creates loop and loads each row of the table into the array called: $row 
   $fname = $row['fname'];//set the value of each field into a variable 
   $lname = $row['lname']; 
   $major = $row['major']; 
   $year = $row['year']; 
   $address = $row['address']; 
   $phone = $row['phone']; 
   $number = $row['number'];
   $email = $row['email'];
   $big = $row['big']; 
   $little = $row['little'];
   $lttle2 = $row['little2']; 

   echo "Row $i:\nfname = $fname\nlname = $lname\nmajor = $major\n $major\nyear = $year\naddress = $address\nphone = $phone\nnumber =$number\nemail = $email\nbig =$big\nlittle = $little\nlittle2 = $little2\n"; 
   $i++; 
}

?>

but it gives me the output sort of i guess

Row 1: fname = lname = major = year = address = phone = number = email = big = little = little2 = Row 2: fname = lname = major = year = address = phone = number = email = big = little = little2 = Row 3: fname = lname = major = year = address = phone = number = email = big = little = little2 = Row 4: fname = lname = major = year = address = phone = number = email = big = little = little2 = Row 5: fname = lname = major = year = address = phone = number = email = big = little = little2 = Row 6: fname = lname = major = year = address = phone = number = email = big = little = little2 = Row 7: fname = lname = major = year = address = phone = number = email = big = little = little2 =

[helz]

not quite the \n signifies a newline, acts the same as the html tag: <br>
also you keep on putting single quotes around the table name in the query!!
it should be:
$sql = "SELECT * FROM test ORDER BY fname ASC LIMIT 0 , 15";
you dont need to have that $i=1; and $i++; part, i only put that in to show which row was which, most of the time the best thing to do as i have found is to add an extra field into the table called: ID it should be of int type and set to Primary Key and Auto-Increment
that way each record that is added is assigned an id number which is unique to that record. which helps significantly when it comes to editing/deleting records

[corruption]

No output grabbed

<?php
include 'conn.php';
$sql = "SELECT * FROM test ORDER BY fname ASC LIMIT 0 , 15";

$result = mysql_query($sql);


while ($row = mysql_fetch_row($result)) {
//creates loop and loads each row of the table into the array called: $row 
//set the value of each field into a variable    

   $fname = $row['fname'];
   $lname = $row['lname']; 
   $major = $row['major']; 
   $year = $row['year']; 
   $address = $row['address']; 
   $phone = $row['phone']; 
   $number = $row['number']   

   $email = $row['email'];
   $big = $row['big']; 
   $little = $row['little'];
   $lttle2 = $row['little2']; 

  echo ($fname,$lname,$major,$year,$address,$phone,$number,$email,$big,$little,$little2\n\n); 
   }
?>

[LordShryku]

test for errors...

$result = mysql_query($sql) or die(mysql_error());

[helz]

it would have spit out an error anyway wouldnt it?
something along the lines of:
there is an error in your mysql syntax at blah blah,
the or die(mysql_error()); just gives a better description of the problem.
i wonder if corruption did get an error because that code looks fine, either that or his table is empty 🙂

[LordShryku]

It depends on your error_reporting level. It won't always spit out an error....

[helz]

ah.
if i remember correctly the default setting is quite low, and for the errors you have to turn some on in php.ini

[corruption]

no errors were returned, since i'm on the schools server i can't change php.ini, they have them set for a reason.

[LordShryku]

Did you use mysql_error like I showed you?

[corruption]

yes, and for clarification the table is not empty,
just for update sakes the script look slike this now

<?php
include 'conn.php';
$sql = "SELECT * FROM test ORDER BY fname ASC LIMIT 0 , 15";

$result = mysql_query($sql) or die(mysql_error());


while ($row = mysql_fetch_row($result)) {
//creates loop and loads each row of the table into the array called: $row 
//set the value of each field into a variable    

   $fname = $row['fname'];
   $lname = $row['lname']; 
   $major = $row['major']; 
   $year = $row['year']; 
   $address = $row['address']; 
   $phone = $row['phone']; 
   $number = $row['number']   

   $email = $row['email'];
   $big = $row['big']; 
   $little = $row['little'];
   $lttle2 = $row['little2']; 

  echo ($fname,$lname,$major,$year,$address,$phone,$number,$email,$big,$little2\n\n); 
   }
?>

[LordShryku]

I see now. mysql_fetch_row returns a numerical array, not associative. Change mysql_fetch_row to mysql_fetch_array