Page 1 of 1

making a SQL powered website

Posted: Sat Jun 19, 2004 7:53 pm
by jeggah
This may sound a bit confusing so I'll use an example.

Say I want to make a book-review website using PHP, and I want to write lots of book reviews. Instead of creating a page for every book, which could mean hundreds and hundreds, is there a way you can do this with mySQL and if so, how?

I would prefer to maybe fill out a form on a website with the 'book-review' which would enter the data into the database, and then you will be able to access this 'book-review' using a URL (in your website template of course)

Please reply and help me

Posted: Sat Jun 19, 2004 8:38 pm
by tim
make links to a page, like

index.php?book=3

use the $_GET method, connect to the sql database where the book = $_GET

hope that wasnt tooooo confusing, if it is let me know and i'll show some examples

Posted: Sat Jun 19, 2004 10:15 pm
by jeggah
im a begginner at all this as you can tell :D so a few examples would be much appreciated!

thank you for replying and helping me!

Posted: Sat Jun 19, 2004 11:56 pm
by infolock
well i'll give you an example, but there are just some things you are going to *have* to do on your own. And trust me, these things aren't something that would take an einstien to figure out... But basically, you are going to need to read the MySQL manual included with the distribution file, along with http://www.php.net/mysql function list to get a basic understanding of how to connect to mysql, and request information from tables. You are probably also going to have to look into http://www.php.net/arrays..

but anyways, here would be an easy/quick example (assuming you have a table setup to work with this code.. )


add_book.htm

Code: Select all

<html>
<head><title>Book Administration</title></head>
<body>
<form name="add_book" method="post" action="add_book.php">
<table>
  <tr>
    <td>Book Name</td>
    <td><input type="text" name="name"></td>
  </tr>
  <tr>
    <td>Author's Name</td>
    <td><input type="text" name="author"></td>
  </tr>
  <tr>
    <td>Date Published</td>
    <td><input type="text" name="date"></td>
  </tr>
  <tr>
    <td><input type="submit" name="submit" value="Add Book"></td>
  </tr>
</table>
</form>
</body>
</html>

add_book.php

Code: Select all

<?php
  if(!isset($_POST['name']) || !isset($_POST['author']) || !isset($_POST['date']))
  {
      echo 'All Fields Must Be Entered Before This Book Can Be Added!<br />Please Try Again! <br /><br /><a href="add_book.htm">Back</a>'
      exit;
   }
   $name = $_POST['name'];
   $author = $_POST['author'];
   $date = $_POST['date'];

   mysql_connect('localhost','username','password');
   mysql_select_db('my_books_db');

   $sql = "INSERT into book_collection (Book_Title, Author, Date_Published) VALUES ('".$name."','".$author."','".$date."')";
   $result = mysql_query($sql) or die(MySQL_Error());

   echo 'Book Successfully Added!<br /><br /><a href="add_book.htm">Add Another Book</a>';
?>

Search.htm

Code: Select all

<html>
<head><title>Search for a Book!</title></head>
<body>
<form name="search" method="post" action="search.php">
<table>
  <tr>
    <td>Enter a little something to search for</td>
    <td><input type="text" name="search_box"></td>
    <td><input type="submit" name="submit" value="Search">
  </tr>
</table>
</form>
</body>
</html>
search.php

Code: Select all

<?php

   $search = $_POST['search'];

   mysql_connect('localhost','username','password');
   mysql_select_db('my_books_db');

   $sql = "SELECT * from book_collection where (Book_Title LIKE '%".$search."%') or (Author LIKE '%".$search."%') or (Date LIKE '%".$search."%')";
   $result = mysql_query($sql) or die(MySQL_Error());
   $num_rows = mysql_num_rows($result);
   if($num_rows == '0')
   {
        echo 'No Matches Found!';
        exit;
   }

    $Book_Title = array();
    $Author = array();
    $Date = array();
    $Unique_ID = array();

    while($row = mysql_fetch_assoc($result))
    {
        $Book_Title[] = $row['Book_Title'];
        $Author[] = $row['Author'];
        $Date[] = $row['Date'];
        $Unique_ID[] = $row['unique_id'];
     }
     echo '<h3>Found '.$num_rows.' matches!';
     echo '<table><tr><td>Book Title</td><td>Author</td><td>Date Published</td><td>View Book</td></tr>
     for($i=0; $i<count($Book_Title); $i++)
     {
        echo '<tr><td>'.$Book_Title[$i].'</td><td>'.$Author.'</td><td>'.$Date.'</td><td><a href="view.php?id='.$Unique_ID.'">View Book</a></td></tr>';
     }
     echo '</table>';
?>
view.php

Code: Select all

$ID = $_GET['id'];

   mysql_connect('localhost','username','password');
   mysql_select_db('my_books_db');

   $sql = "SELECT * from book_collection where unique_id = '".$ID."'";
   $result = mysql_query($sql);

   echo '<h3>Book Information</h3>';
    $Book_Title = array();
    $Author = array();
    $Date = array();
 
    while($row = mysql_fetch_assoc($result))
    {
        $Book_Title[] = $row['Book_Title'];
        $Author[] = $row['Author'];
        $Date[] = $row['Date'];
     }
    echo '<table><tr><td>Book Title</td><td>Author</td><td>Date Published</td></tr>
     for($i=0; $i<count($Book_Title); $i++)
     {
        echo '<tr><td>'.$Book_Title[$i].'</td><td>'.$Author.'</td><td>'.$Date.'</td></tr>';
     }
     echo '</table>';
?>

Hope this helps.

Posted: Sun Jun 20, 2004 7:03 am
by jeggah
Thanks, this is really helpful but unfortunately I have come across errors that I cannot solve.

The add_book code was fine if removed the word "exit" from the code but then ti worked.

But on Search Books, I come across this message

Parse error: parse error, unexpected '>' in /home/yesyes/public_html/book/search.php on line 33

Could anyone be so kind as to help me?

Posted: Sun Jun 20, 2004 9:12 am
by tim
echo '<table><tr><td>Book Title</td><td>Author</td><td>Date Published</td><td>View Book</td></tr>

the echo statement was never ended

add a ';


:wink:

Posted: Sun Jun 20, 2004 11:41 am
by jeggah
Thanks for pointing that out!
But now I get this problem

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

for the search.php and view.php!

I tried to find the error but unfortunately failed :(
Can anyone help?

Posted: Sun Jun 20, 2004 12:38 pm
by tim
always add

or die(mysql_error()); to your querys/anything sql related, they'll tell you whats going on with them.


Also - never use fetch_assoc, use fetch_array

mysql_fetch_array returns two types of forms of data, assoc does one.

Posted: Sun Jun 20, 2004 12:47 pm
by feyd
I always use fetch_assoc when selecting all fields, unless I'm selecting 1 field (at which point I'll use fetch_row). fetch_array wastes time writing each element twice.

Posted: Sun Jun 20, 2004 12:51 pm
by tim
the time (i think) is not noticable between the two. I just rigged myself in the habit of using fetch_array over assoc.

Posted: Sun Jun 20, 2004 3:03 pm
by jeggah
OK thats cool, I sorted it out, i never had a unique_id column thats why. But now when I try to do the search, it displays all of the books and it only has the book name.
In every other row it says Array

So I suppose it's a problem to do with the arrays, any suggestions?

Posted: Sun Jun 20, 2004 6:12 pm
by feyd
$Array[$i] and $Date[$i]