while queries

Jermizzle

I have two queries. I need to run these queries so data from both tables can be displayed in one table on the web.

Here are the queries: -->

$query_data = "SELECT * FROM doc_data ORDER BY session ";

$result = mysql_query($query_data) or die ("ERROR:Unable To Run Query".mysql_error());

$fin_rate = "SELECT * FROM fin_rate";

$result_fin = mysql_query($fin_rate) or die ("ERROR:Unable To Select Finance".mysql_error());

And i need to fit both queries into a while function

while ($row=mysql_fetch_assoc())

I have tried placing these queries into the while funtions numerous ways but it keeps on messing up the table. Here are the ways i have tried.

$query_data = "SELECT * FROM data_doc,fin_rate ORDER BY session";

$result_data = mysql_query($query_data) or die ("....");

while ($row=mysql_fetch_assoc($result_data)){

This places everything in the table nicely but it duplicates the data. So when the field only has 3 items it will show six. So i thought it was because i had identical fields in doc_data and fin_rate so i changed the fieldname but it made no difference so it cannot be that.

$query_data = "SELECT * FROM data_doc ORDER BY session";

$result_data = mysql_query($query_data) or die ("....");

$query_finrate = "SELECT * FROM fin_rate";

$result_finrate = mysql_query($query_finrate) or die ("...");

while ($row=mysql_fetch_assoc($result)){
	echo "<tr>";
	echo "<td><p align=center><font face=Verdana size=-1>".$row['session']."</center></td>";
	echo "<td><font face=Verdana size=-1><a href=http://pws.com/$row[url]><font face=Verdana size=-1>".$row['idea']."</td>";
	echo "<td><font face=Verdana size=-1>".$row['inventor_name']."</td>";
}
while ($row=mysql_fetch_assoc($result_finrate)){

echo "<td><select name=".$ratefin['finrate_id']."> <option value= '' selected></option>\n 
	<option value=1>1 - Poor</option>\n
	<option value=2>2 - Fair</option>\n
	<option value=3>3 - Average</option>\n
	<option value=4>4 - Good</option>\n 
	<option value=5>5 - Excellent</option>\n
	</select>\n</td>";
		if ($ratefin['fin_rating'] > 0){
			echo "<td><p align=center><img src=reddot.gif name=already_rated height=10px width=10px></td>";
		}else {
			echo "<td><p align=center><img src=bluedot.gif name=need_rating height=10px width=10px></td>";
			}
} 
echo "</tr>\n";

The section under $row=mysql_fetch_assoc($result)) is fine but then when $row=mysql_fetch_assoc($result_finrate)) is being displayed everything is shown in columns instead of rows.

Does anyone no how this can be done?

voidstate

Sounds like you need to join the data in your query. How do doc_data and fin_rate relate to one another?

Could you post the column names form both tables?

Jermizzle

Yeap i want to join data from both queries without the table coming out of place.

Here are the properties for each attribute in both tables.

doc_data

doc_id int unsigned NOT NULL auto_increment primary key,
session varchar(10) NOT NULL,
idea varchar(255) NOT NULL,
inventor_name varchar(255) NOT NULL,
url varchar(255) NOT NULL,
filename varchar(255) NOT NULL

fin_rate

finrate_id int unsigned NOT NULL auto_increment primary key,
idea varchar(255) NOT NULL,
fin_rating int,
num_of_votes int

I first thought it was because both tables have {idea} within them so i changed idea in the fin_rate table to fin_idea but the data was still duplicated. I think its just the way the table has been arranged in php but i have been moving around <tr> and <td> as well as the while function but it doesnt seem to fix it.

voidstate

So, is this right - You have some ideas, each of which has a document and can be rated.

If that's the case, you need to use the keys to define which ratings relate to which document. I'd add a new column in fin_rate called doc_id (this is called a secondary key - it's the key from another table) which would tell you which document that rating is related to.

Then you can use a single query:

SELECT * FROM doc_data, fin_rate WHERE doc_data.doc_id = fin_rate.doc_id ORDER BY session

voidstate

Jermizzle

Yeap thats exactly what i want to do.

I am having trouble populating the doc_id field in fin_rate table. I hav a form which places the ideas into a database. So every time a new idea is added the doc_id in the doc_data table goes up by 1. I have not placed a input box onto the form for doc_id because its an auto_increment field. How can i grab the doc_id(pk) from doc_data and place it into the doc_id (fk) in fin_rate?

I was using INSERT fin_rate (doc_id) values ('$doc_id') but thats only works if i am sending doc_id from the form.

Thanks

voidstate

mysql_insert_id will give you the doc_id. Just call it after you insert into the doc_data table.

http://uk.php.net/manual/en/function.mysql-insert-id.php

Jermizzle

$insert_fin = "INSERT INTO fin_rate(idea,fin_rating,num_of_votes,doc_id) values ('$idea',0,0,'$id')";

$insert_tech = "INSERT INTO tech_rate(idea,tech_rating,num_of_votes,doc_id) values ('$idea',0,0,'$id')";

$result_tech = mysql_query($insert_tech) or die ("ERROR: Unable to update idea in technical table".mysql_error());

$result_fin = mysql_query($insert_fin) or die ("ERROR: Unable to update idea in finance table ".mysql_error());

$id = mysql_insert_id();

When i try to run this i get:

Out of range value adjusted for column 'doc_id' at row 1

When i read this up it says i am getting this error because im trying to submit an empty variable which it wont allow.

But i do not know how this is possible because each variable is being populated with something from the form xcept the mysql_insert_id(). I tested this out by using echo $id and i keep on getting zero even though i have 7 records in the table. What is the problem?

Help ???

Jermizzle

I've worked it out, i was putting the function under the wrong query.

Jermizzle

voidstate, you are a bloody legend. Its working, and the table is look very good.

Thank You Very Much 🙂