Page 1 of 1

[SOLVED] Can't get the array to display - mysql_fetch_array

Posted: Mon Nov 24, 2003 9:42 pm
by jim_73_mk1
Hello...

I'm new to both SQL and PHP and having a dickens of a time getting any info back out of MySQL using PHP. I want to use a loop to check for a duplicate variable ($ISBN) in the ISBN column of the table books. I can't get any of the loops to print or echo the values in any part of the table let alone work the test code. My query when run from the MySQL command line lists all the data in the column ISBN. What am I doing wrong?

Below is the COD I am using:

Code: Select all

// Storing the posted names from the form of the previous page
$ISBN = $_POST["ISBN"];
$Title = $_POST["Title"];
$Auth = $_POST["Auth"];
$AuthURL = $_POST["AuthURL"];
$Pub = $_POST["Pub"];
$PubURL = $_POST["PubURL"];
$Desc = $_POST["Desc"];
$Personal = $_POST["Personal"];

$link = mysql_connect("localhost","$userID","$pw")
	or die ("Unable to connect to MySQL server.");  // This works!!
$db = mysql_select_db("$dBase")
	or die ("Unable to select requested database."); // This works!!
// The line below works fine from MySQL command line
$query = "SELECT ISBN FROM Books" 
$result = mysql_query("$query") // Also working - no error!!
	or die ("Unable to query ISBN.");

//Here is wehre the problems begin...
//This sould print out the number of rows repeatedly
while ($array = mysql_fetch_array($result)){
	$data = count($array);
	print ("<br>the Result: " . $data); 
}

// These lines enter the new values into the database table AND WORK!!
$Insert = mysql_query("INSERT INTO books (ISBN, Title, Author,
	AuthorURL,Publisher, PublisherURL, Description, Personal)
	VALUES ('$ISBN', '$Title', '$Auth', '$AuthURL', '$Pub', '$PubURL',
	'$Desc', '$Personal')") or die (mysql_error());	
print ("<center><h1>Your book listing has been posted.</h1></center>");
// This line closes the database server connection
mysql_close($link);

Posted: Mon Nov 24, 2003 10:40 pm
by DuFF
What happens when you do this:

Code: Select all

<?php
while ($array = mysql_fetch_array($result)){
print_r($array);
$data = count($array);
print ("<br>the Result: " . $data);
} 
?>

Posted: Tue Nov 25, 2003 12:38 am
by mrvanjohnson
Hum, I’m not sure why you would want to print it out repeatedly but to get the count on how many rows a select statement returns you would do this

Code: Select all

<?php
$result = mysql_query("SELECT ISBN FROM Books" );
$num_rows = mysql_num_rows($result);
echo "Number of rows returned are $num_rows" ;

// Then to display the data in the table you would do something like this
// Assuming your column in you database is called ISBN
 
while ($row = mysql_fetch_array($result)){ 
echo "ISBN =";
echo $row['ISBN'] ;
}

?>
I think that's all right, it's late and I am going off the top of my head I'm sure someone will correct me if I'm wrong.

Posted: Tue Nov 25, 2003 1:22 am
by infolock
if you are looking for duplicates, this may be of use :

Code: Select all

<?php

$sql = "select count(*) as repetitions, ISBN from books group by ISBN having repetitions > 0";
$result = mysql_query($sql);

// then it's just a matter of spitting it out...
while ($row=mysql_fetch_assoc($result)
{
   echo $row['ISBN'].' was found '.$row['repetitions'].'  times!';
   echo '<br />';
}

?>

Thank you all for the help!!

Posted: Fri Nov 28, 2003 7:50 am
by jim_73_mk1
I wish to thank all those who posted replies. Your code snippits pointed out my error. Here is the new code and how I fixed it:

Code: Select all

while ($array = mysql_fetch_array($result)){
	if ($ISBN == $array['ISBN']) { // This is the line that needed fixing!
		print (
		"<br>The ISBN of the book you have entered matches a book<br>
		in the Database.  Duplicate ISBN numbers are not allowed. <br>
		Please make sure the ISBN is correct.");
		$Post = "False";
		if ($Personal == 1){
			$Desc = "jepjr" . $Desc;
		}
		break;
	}
}

Posted: Fri Nov 28, 2003 8:05 am
by Cruzado_Mainfrm
Note this, in mysql_connect, you do not need to put the variable in quotes, just mysql_connect('localhost', $userID, $pw);
same for mysql_select_db()
just one thing more, i think it's tedious having to put capitalized words all the time, you'll save some time using lowercase varibles only :D

Code: Select all

<?php
// Storing the posted names from the form of the previous page 
$ISBN = $_POST["ISBN"]; 
$Title = $_POST["Title"]; 
$Auth = $_POST["Auth"]; 
$AuthURL = $_POST["AuthURL"]; 
$Pub = $_POST["Pub"]; 
$PubURL = $_POST["PubURL"]; 
$Desc = $_POST["Desc"]; 
$Personal = $_POST["Personal"]; 

$link = mysql_connect("localhost","$userID","$pw") 
   or die ("Unable to connect to MySQL server.");  // This works!! 
$db = mysql_select_db("$dBase") 
   or die ("Unable to select requested database."); // This works!! 
// The line below works fine from MySQL command line 
$query = "SELECT ISBN FROM Books" 
$result = mysql_query("$query") // Also working - no error!! 
   or die ("Unable to query ISBN."); 

//Here is wehre the problems begin... 
//This sould print out the number of rows repeatedly 
while ($array = mysql_fetch_array($result)){ 
   if ($ISBN == $array['ISBN']) { // This is the line that needed fixing! 
      print ( 
      "<br>The ISBN of the book you have entered matches a book<br> 
      in the Database.  Duplicate ISBN numbers are not allowed. <br> 
      Please make sure the ISBN is correct."); 
      $Post = "False"; 
      if ($Personal == 1){ 
         $Desc = "jepjr" . $Desc; 
      } 
      break; 
   } 
} 

// These lines enter the new values into the database table AND WORK!! 
$Insert = mysql_query("INSERT INTO books (ISBN, Title, Author, 
   AuthorURL,Publisher, PublisherURL, Description, Personal) 
   VALUES ('$ISBN', '$Title', '$Auth', '$AuthURL', '$Pub', '$PubURL', 
   '$Desc', '$Personal')") or die (mysql_error());    
print ("<center><h1>Your book listing has been posted.</h1></center>"); 
// This line closes the database server connection 
mysql_close($link); 
?>