[SOLVED] my normalized databse - help :)

[gwydionwaters]

so i moved into a new thread because the old one was getting confusing and partially irrelevant. i have a database called gear, in it are the following tables;

items=item_id,title,author_id,cost_id,rate_id,func_id,type_id,description

author=author_id,author

cost=cost_id,cost

rate=rate_id,rate

func=func_id,func

type=type_id,type

 

the stuff i need help with;

**

i am trying to build dynamic select boxes for querying the database

 

here is my form thus far

<?
  include('scripts/dbase.incl.php');
  mysql_connect($hostname,$username,$password);
  @mysql_select_db($database) or die( 'unable to select database, please refresh');
  $query = 'SELECT Type, Rate, Func, Cost, Author FROM items.Item_id=type.Type_id';
  $result = mysql_query($query);
  $num = mysql_num_rows($result);
  mysql_close();
  ?>
//skip the html stuff ...
<form name="abrowse" action="scripts/gearbrowse.php" method="post">Author<BR><select onChange="this.form.submit()" name="Author"><option></option>
     <?
  $i = 0;
  while ($i < $num) {
	  $Author=mysql_result($result,$i,"Author");
 ?>
     <option value="<? echo $Author; ?>"><? echo $Author; ?></option><? $i++ ; } ?></select></form></td>
    </tr>
//there is an entry like this for each search type (cost, type, etc)

 

**

i can no longer figure out how to show the contents of the database as:

  Name, Author, Type, Use(func), Cost, Rating(rate), Description

my showdb query used to be

select * from gear

when gear was a table containing all the info

now i am not sure how to replicate the same results

thanks for reading

[Maq]

You can start out by reading this tutorial on how to extract data from a data base, SELECT.

 

Read further, there are examples of HTML inputs.

[gwydionwaters]

thanks, i checked that one out and according to that the following should return my database in the entirety i want? i may be way off lol

SELECT items.Title, items.Description, author.Author, cost.Cost, type.Type, rate.Rate, func.Func FROM items, author, cost, type, rate, func WHERE items.Author_id=author.Author_id, items.Cost_id=cost.Cost_id, items.Type_id=type.Type_id, items.Rate_id=rate.Rate_id, items.Func_id=func.Func_id

[Maq]

You can do:

 

SELECT * FROM

 

* = everything.

[gwydionwaters]

yes but SELECT * FROM would only allow me to return all of a table, i need to return a specific set of table values from different tables and show these to the user. some tables are just possible values for the items, like cost: there are many possible price ranges but only the ones set for an item should be shown.

 

here is the table that the user would be shown

<table border="0" cellpadding="2">
  <th><H4>Name</H4></th>
  <th><H4>Author</H4></th>
  <th><H4>Type</H4></th>
  <th><H4>Use</H4></th>
  <th><H4>Cost <= $</H4></th>
  <th><H4>Rating X/10</H4></th>
  <th><H4>Description</H4></th>
<?
$i=0;
while ($i < $num) {

$Title=mysql_result($result,$i,"Title");
$Author=mysql_result($result,$i,"Author");
$Type=mysql_result($result,$i,"Type");
$Func=mysql_result($result,$i,"Func");
$Cost=mysql_result($result,$i,"Cost");
$Rate=mysql_result($result,$i,"Rate");
$Description=mysql_result($result,$i,"Description");
?>

  <tr>
   <td><? echo $Title ?></td>
   <td><? echo $Author ?></td>
   <td><? echo $Type ?></td>
   <td><? echo $Func ?></td>
   <td align="center"><? echo $Cost ?></td>
   <td align="center"><? echo $Rate ?></td>
   <td><? echo $Description ?></td>
  </tr>
<?
$i++;
}
?>
   </table>

[gwydionwaters]

my current query to attempt to get the data i want from my database is as follows

$query = "SELECT items.Title, items.Description FROM items INNER JOIN items, author, cost, type, rate, func ON items.Author_id = author.Author_id AND items.Cost_id = cost.Cost_id AND items.Type_id = type.Type_id AND items.Rate_id = rate.Rate_id AND items.Func_id = func.Func_id";

it comes back as not being a valid result resource

[fenway]

That's not proper syntax.

 

SELECT 
items.Title, items.Description 
FROM items 
INNER JOIN author USING (Author_id )
INNER JOIN cost USING ( Cost_id ) 
INNER JOIN type USING ( Type_id )
INNER JOIN rate USING ( Rate_id )
INNER JOIN func USING ( Func_id )

 

Though I don't see the need for the other tables at this point.

[gwydionwaters]

strange, i'm not sure what i was thinking. i needed all the tables because i wanted to only display options which had a relationship to a complete entry. but i gave up and now have full lists but an error if there are no complete entries.

 

i have a new problem though

 

i am trying to now work out a feature, where a user can browse through things like type, cost etc.

i have a page wth what appears to be one form, but really there is a form for each type of value (cost, type ..)

i have one script to handle the data from whatever form is sent, i had it working with just the value 'author' but now that i have tried to add the others it doesn't want to work anymore. the handler script is this

<?
$browse=$_POST['browse'];
$author=$_POST['author'];
$type=$_POST['type'];
$func=$_POST['func'];
$cost=$_POST['cost'];
$rate=$_POST['rate'];
$query = "SELECT items.Title, items.Desc, author.Author, type.Type, func.Func, cost.Cost, rate.Rate FROM items INNER JOIN author ON items.Author_id = author.Author_id INNER JOIN type ON items.Type_id = type.Type_id INNER JOIN func ON items.Func_id = func.Func_id INNER JOIN cost ON items.Cost_id = cost.Cost_id INNER JOIN rate ON items.Rate_id = rate.Rate_id WHERE ";
if ($browse=('author')) {
$var1 = "author = $author";
$query .= $var1;
}
if ($browse=('type')) {
$var1 = "type = $type";
$query .= $var1;
}
if ($browse=('use')) {
$var1 = "func = $func";
$query .= $var1;
}
if ($browse=('cost <=')) {
$var1 = "cost = $cost";
$query .= $var1;
}
if ($browse=('rating')) {
$var1 = "rate = $rate";
$query .= $var1;
}
else {
echo 'Sorry an error occurred, please try again';
}
include("dbase.incl.php");
mysql_connect($hostname,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$result=mysql_query($query);
$num=mysql_num_rows($result);
mysql_close();
?>

thanks for helping

[gwydionwaters]

it seems you only have so long to edit your post anyway, i have changed my script a bit, to try an else if type sequence like so

if ($browse=('author')) {
$var1 = "author = '$author'";
$query .= $var1;
}
elseif ($browse=('type')) {
$var1 = "type = '$type'";
$query .= $var1;
}
esleif ($browse=('use')) {               //**Line 24 **
$var1 = "func = '$func'";
$query .= $var1;
}
elseif ($browse=('cost <=')) {
$var1 = "cost = '$cost'";
$query .= $var1;
}
elseif ($browse=('rating')){
$var1 = "rate = '$rate'";
$query .= $var1;
}
else 
echo 'Sorry an error occurred, please try again';
endif;

but  get a parse error on line 24, unexpected {

which makes no sense as the syntax on that elseif is the same as the previous which is not a parse error ... confusing stuff lol

[Maq]

1)

esleif ($browse=('use')) {  

 

Should be (notice the mispelling of "elseif"):

 

elseif ($browse == 'use') {  

 

1) You're assigning instead of comparing.  All the single equal signs should be double.  Example:

 

if ($browse=('author')) {

 

Should be

 

if ($browse == 'author') {

 

2) endif?  This is PHP not VB.