PHP coding problem with mysql database

database name: test
table name: test
fields: - ID
- Title
- Body
- Author

I want to display the title and the author of all the records with 10 results per page. I used the following code but got an error.

Can somebody tell me what I am doing wrong ? I've tried to figure it out for two days now but haven´t found the answer yet (might be important to add that I'm a newbie in this).

Code:

<? 
// Create database connection and select database 
mysql_select_db('test', mysql_pconnect('localhost','root','')) or die (mysql_error()); 

// If current page number, use it 
// if not, set one! 

if(!isset($_GET['page'])){ 
    $page = 1; 
} else { 
    $page = $_GET['page']; 
} 

// Define the number of results per page 
$max_results = 10; 

// Figure out the limit for the query based 
// on the current page number. 
$from = (($page * $max_results) - $max_results); 

// Perform MySQL query on only the current page number's results 

$sql = mysql_query("SELECT * FROM pages LIMIT $from, $max_results"); 

while($row = mysql_fetch_array($sql)){ 
    // Build your formatted results here. 
        echo $row['title']. - "
      </font color >";
 echo $row['Author']." <br /><br />
      </font color >";  
} 

// Figure out the total number of results in DB: 
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM pages"),0); 

// Figure out the total number of pages. Always round up using ceil() 
$total_pages = ceil($total_results / $max_results); 

// Build Page Number Hyperlinks 
echo "<br><center>Select a Page<br />"; 

// Build Previous Link 
if($page > 1){ 
    $prev = ($page - 1); 
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\"><<Previous</a> "; 
} 

for($i = 1; $i <= $total_pages; $i++){ 
    if(($page) == $i){ 
        echo "$i "; 
        } else { 
            echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> "; 
    } 
} 

// Build Next Link 
if($page < $total_pages){ 
    $next = ($page + 1); 
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next\">Next>></a>"; 
} 
echo "</center>"; 
?> 

PHP:

This is what i get on my .php page

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in c:\appserv\www\test2.php on line 25

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in c:\appserv\www\test2.php on line 34


Select a Page

 

it would seem that the error in line 25 is due to line 23:

original line 23:
$sql = mysql_query("SELECT * FROM pages LIMIT $from, $max_results");

change to:
$sql = mysql_query("SELECT * FROM pages LIMIT " . $from . ", " . $max_results);

as for line 34, try replacing with:

$rst = mysql_query("SELECT COUNT(*) as Num FROM pages");
$total_results = mysql_result($rst, 0);

 

Still having problems:: made the changes you suggested but I still get an error message.

Code:

<? 
// Create database connection and select database 
mysql_select_db('test', mysql_pconnect('localhost','root','')) or die (mysql_error()); 

// If current page number, use it 
// if not, set one! 

if(!isset($_GET['page'])){ 
    $page = 1; 
} else { 
    $page = $_GET['page']; 
} 

// Define the number of results per page 
$max_results = 10; 

// Figure out the limit for the query based 
// on the current page number. 
$from = (($page * $max_results) - $max_results); 

// Perform MySQL query on only the current page number's results 
$sql = mysql_query("SELECT * FROM pages LIMIT " . $from . ", " . $max_results);

while($row = mysql_fetch_array($sql)){ 
    // Build your formatted results here. 
        echo $row['title']. - "
      </font color >";
 echo $row['Author']." <br /><br />
      </font color >";  
} 

// Figure out the total number of results in DB: 
$rst = mysql_query("SELECT COUNT(*) as Num FROM pages");
$total_results = mysql_result($rst, 0);


// Figure out the total number of pages. Always round up using ceil() 
$total_pages = ceil($total_results / $max_results); 

// Build Page Number Hyperlinks 
echo "<br><center>Select a Page<br />"; 

// Build Previous Link 
if($page > 1){ 
    $prev = ($page - 1); 
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\"><<Previous</a> "; 
} 

for($i = 1; $i <= $total_pages; $i++){ 
    if(($page) == $i){ 
        echo "$i "; 
        } else { 
            echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> "; 
    } 
} 

// Build Next Link 
if($page < $total_pages){ 
    $next = ($page + 1); 
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next\">Next>></a>"; 
} 
echo "</center>"; 
?> 

PHP:

Error:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in c:\appserv\www\test2.php on line 24

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in c:\appserv\www\test2.php on line 34

 

That usually means that there is nothing in the result.

Have you made sure;

- there are entries in the database
- the table names are all correct


As far as I can see you're trying to get results from a table called pages when your table is called test.

 

not true... even if there is no result, the returned resultset should still be a valid one... albeit empty...

do a "print $sql"; to see if your sql statement was assembled properly... you've got variables in the assembly of your sql statement and they may not have been set properly...

 

Sometimes I have made a mistake in my query which has resulted in there being no results but I guess there maybe an error value assigned to the result variable.

My bet on what the problem is from what Smetten has stated above that the table name needs to change from pages to test.

 

echo back the following line to yourself.

$rst = mysql_query("SELECT COUNT(*) as Num FROM pages");

PHP:

IIRC this should tell you a number and make the line after it

$total_results = mysql_result($rst, 0);

PHP:

not necessary. I could be wrong though, but, without being able to run the code I cannot see.

 

Problem solved,
thx for all the replies

Greetz
Smetten

 

there are potential security problems with ur script

#
if(!isset($_GET['page'])){
#
    $page = 1;
#
} else {
#
    $page = $_GET['page'];
#
} 

PHP:

what if someone starts meesing around and tries break your script??
your scrip only checks if the value is set

lets say they pass in a string or a negative number?? ull be screwed then
always check for values that u dont expect and especially if the value will be used in a database query

heres a sample from one of my scripts

if( !$_GET['thread_id'] || $_GET['thread_id'] <0 || !ctype_digit($_GET['thread_id']) ){  // Check threadid is correct
	$Message->setMessage('You are not allowed to view this page.', '?do=index');
	$Message->displayMessage('?do=message');
}
else{
	$thread_id = $_GET['thread_id'];
}

PHP:

 

Thanx for pointing that out to me.
For the moment I'm just experimenting on my own pc.
I never used mysql/php before.

I'm planning to build a website later on.
Guess I´ll have to take some time to learn about the security settings also, as I don´t want people to ruin my site.

Greetz

Smetten

 

the best tip i can give you once you go live

is not to thrust any user input
check for as many posssible values as possible
especially if the values will be used in a database query

send as many variables by post as possible than using get

try to break the script yourself
even better if you trust ur users ask them to give feedback

in my case i got loadz f bloppers pointed out, if users like what you have they will help you, users also like that they are being listened to


on all the forms on your sites that get submited by unregistered users also add image verification, for example on logon and registration.