making a SQL powered website

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jeggah
Forum Newbie
Posts: 12
Joined: Sat Jun 19, 2004 7:53 pm

making a SQL powered website

Post 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
User avatar
tim
DevNet Resident
Posts: 1165
Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio

Post 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
jeggah
Forum Newbie
Posts: 12
Joined: Sat Jun 19, 2004 7:53 pm

Post 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!
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post 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.
jeggah
Forum Newbie
Posts: 12
Joined: Sat Jun 19, 2004 7:53 pm

Post 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?
User avatar
tim
DevNet Resident
Posts: 1165
Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio

Post 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:
jeggah
Forum Newbie
Posts: 12
Joined: Sat Jun 19, 2004 7:53 pm

Post 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?
User avatar
tim
DevNet Resident
Posts: 1165
Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
tim
DevNet Resident
Posts: 1165
Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio

Post 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.
jeggah
Forum Newbie
Posts: 12
Joined: Sat Jun 19, 2004 7:53 pm

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

$Array[$i] and $Date[$i]
Post Reply