PHP :: JOIN Table Results W/MySQL

Table Background Color Based On Results From MySQL Table
I am trying to get the "Did User Agree" field on a database query to be colored Green for "Agreed" Red for "Disagreed" and Blue for "Agreed (2nd Time)" .. I have my PHP setup to do the query just fine as of right now with alternating column colors, just can't seem to get the colors to work as I am hoping for. Can anyone help me get the Agreed? table's background color to the colors above? Code:

Divide Mysql Results Into Rows In An Html Table
I need to divide my mySQL results into rows with 4 results on each row. Its for a small image gallery script, I wanna show 4 thumbnails per row. I have all the code ready to get the info and show it, I just need an example of how I can take the data from the table and have it automatically go to a new row every 4 entries.

Table Join
I used this SQl query to join two table that have usedID and msepcdp userid in common.
"SELECT * FROM users,msepcdp WHERE users.ID = msepcdp.userid AND msepcdp.type LIKE 'music"
Wheni i try to echo the the fields using:

"$query2 = "SELECT * FROM users,msepcdp WHERE users.ID = msepcdp.userid AND msepcdp.type LIKE 'music'"; Code:

Table Join
The table WALKS lists walks where some records have photos available. To save searching through 750+ records to find those with photos, I have another table ALBUM with walks.walk_id listed in the column walk. The MySQL query that follows isn’t working. What am I doing wrong? Code:

Trouble With Join Query's, Query Just Dosent Produce Results
I have the following code which I am having difficulty getting to
work. I think it may be a problem with the $got query that is being
run as if I set that to a set value then my site seems to run ok, but
I also have checked the data whichi is being held in $detailsrow[1] by
echoing them and it is perfect. I have also sutstituted the values and
ran the query in mysqql directly and it gives me the exat results I am
expecting!

Ive been on it for hours now and I cant get it to work so any help
will be fantastic,

<select name="type">
<?PHP
$mod = ("select name from modules");
$modnames = @mysql_query($mod);
$getfulltype = ("select modules.name from modules, devices where
modules.code= $detailsrow[1] and devices.type = $detailsrow[1]");
$got = mysql_query($getfulltype);
while($row = mysql_fetch_row($modnames))
{
if ($got == $row[0])

{

echo ("<option selected value="$got">$got</option> ");
}

else {
echo ("<option value="$row[0]">$row[0]</option>");
}
}
?>
</select>

Table Join Count Records.
got my counting records script just the way I want it, now I've read all the posts on Joins, and read the manual and mysql.com.... but can't seem to get the right number to come out.

I have two tables: table1 & table 2

table one has a field called ID and field two has a field called relID (this is the link between them) I want to count all of the records in table two where the relID is equal to the table 1 ID field. I thought this would do it, but the number it returns is wrong:
PHP Code:

Mysql - SUM And INNER JOIN
Can anyone help with this query? Here is what I am trying to do:

mysql_query("SELECT code.* FROM code INNER JOIN ratings ON code.id = ratings.codeid WHERE SUM(ratings.rating) >= 50 ORDER BY code.id DESC");

Obviously this won't work because you cant use SUM in the WHERE clause, but you can see what I am trying to do. Any ideas?

MySQL Join Question
Can't seem to figure this out.

I have a MySQL query where I have Table A joined to Table B.

Now, I need the rows from A more than I do B, put another way, I need the rows from Table A no matter what happens getting rows from Table B.

Again, put another way, no matter whether a record doesn't exist in B, does exist in B but doesn't meet the WHERE criteria, or anything at all, I need the records from A no matter what.

I have tried using LEFT JOIN and LEFT OUTER JOIN but they both don't work when records exist in B but don't meet the where criteria, therefore the JOIN fails and I lose the record from A.

Is there anyway to get the JOIN I need in one query or will I need to use more than one?

LEFT JOIN (MySQL)
=============
The problem:
=============
I want to list the users with their country and region (If the user's country has no regions, NULL would be returned).

ex:

userId = 123
country = USA
region = New York

userid = 456
country = Japan
region = NULL

Japon has no regions (in my application) so "NULL" is returned.

=============
The Tables:
=============

Best Way To Show A Mysql Join
I have the following tables:

students
id, name,....

lessons
id,description,....

studentslessons
id,studentid,lessonid,grade

I want to list the lessons of student with name="John" for example.

The one way to do it is :
select id from students where name="John" and grab the id. then select *
from studentslessons where studentid=id;

the other is
select * from students,studentslessons where students.name="john" AND
students.id=studentslessons.studentid

Which is more efficient when using php4 and mysql 4.1? I'm using a
rather old computer Pentium 800MHz.

Mysql JOIN Syntax
I am trying to JOIN two tables together using the common column "Username". My code is ECHOing all of rows of column "title". I want to just ECHO the column "title" rows that are specific to the "Username".

example: I have a user named "ballhogjoni". This username is stored in a table named 'users' and in a table named 'products' under the column "Username". There is a "Username" column in each table. Code:

Variables From JOIN In Mysql
just a question on when i join 2 tables in mysql, say both of my tables have an 'id' (which most of the time they do)

if i do:

$id = $row['id'];
how can i distinguish between the id's? i mean if table 1 has and id and table 2 has an id how would i set the variable?

Need A Thorough Explanation Of A MySQL Left Join
I have been scouring the internet trying to find a good, solid, visual
explanation of a left join. I "kind of" get it but I get confused.

If I could see a visual - I get confused by the on clause.

I have a database of articles. 1 table is the article itself.
Another is article categories, and the last is articles in categories
that records the article's ID and the category ID.

So I have a left join that someone else wrote that I'd desperately
like to understand that looks like this:

Retrieveing Vars From Mysql Join?
I have the following problem :

$some_query = mysql_query(
"SELECT table1.id, table1.category, table1.author, table1.title,
table2.title, table2.category FROM table1, table2
WHERE table1.category = table2.category ORDER by table1.id DESC");

while($record = mysql_fetch_array($some_query))
{
$mycategory = ($record['table1.category']);
$mytitle = ($record['table2.title']);

echo"$mycategory";
echo"$mytitle";
}

Does someone know what's wrong here ?
echo don't show the requested values ....

What Join To Use For Joining Three Tables In Mysql?
I have person Table which holds address key that points to "link-table" record which holds person ID and address table record ID. I want to get precise person and precise address so I thing that is disqualifying the LEFT and RIGHT joins. Remains the INNER join but I did not see it anywhere with more then two tables.

My creation: "SELECT t1.name, t2.* FROM persons AS t1, addresses AS t2, persons_addresses as t3 WHERE t3.id = t1.address AND t2.id = t3.address" does not work the way I want.(IT is basically left join [or not?]) Please, can any mysql-php GURU help me?

person id = persons_addresses.person
person ... field address
persons_addresses id = person.address
persons_addresses ... field address
addresses id = persons_addresses.address

Table address holds all relevant info about the address like street, zip, city key, state and country. I can imagine the use of subselect if mySQL had one, but I can not actually imagine without it [and probably is only very simple matter].

Displaying Select Multiple Box And Using Mysql Join
i have an edit page where the query lists all the information from the database about a business. each business can have multiple types so i made a many-to-many database and now i need to be able to show all the types that are in the db for each business on the edit page. here is what i have so far but it's only selecting the last type of business in the result. PHP Code:

Get Most Recent Row On An Inner Join Mysql Query Based On Date
Here is my current query:

SELECT tbl1.id, tbl1.username, tbl1.status, tbl1.joined, tbl2.timeDate, tbl1.directory FROM tbl1, tbl2 WHERE tbl1.id = tbl2.users_id GROUP BY tbl1.id

This returns their oldest ie first login. I use this for analytics on the backend to determine user activity. So I would like a query that instead that returns the most recent tbl2.timeDate.

Place The Results In Another Table
I am trying to take the results from a query and place the results in another table.
I want to take all the results from $row join them together. is this possible to do?
And can the results be stored with the information from the print statement? (<br> from each row) Code:

Colour Top 3 Results In A Table
I'm currently trying to make a poker league whats seem to be going well.

I have populated a table with the league results, but would like 1st position in gold, 2nd in silver and 3rd in bronze. These will be the first three rows of the table, as the data has already been arranged in the correct order.

I'm a little unsure how to go about doing this, and wondered if anyone could point me in the right direction. Code:

Sorting Table Results
I tried searching Google for some stuff about sorting table results.
I want to be able to click the different headers and sort the results by each field. Anyone know some good sites/tutorials?

Dynamically Generated Results Table
I'm trying to write a script that dynamically generates HTML tables depending on the results from an SQL query. So far, I have managed to set the HTML table headers to be the same as those in the MySQL table. [thanks to rod k -cheers !]

Now i'm trying to put the results in the table. Basically, I need a MySQL function to retrieve data and put it into the table until there is no data left....

(At the moment I am using "mysql_fetch_array()", but with this it is necessary to know the names of the columns to retrieve the information for each result set. Since my table is dynamic and should be able to build itself from different MySQL tables, the column names aren't fixed and so i can't use this to put the data in the tables...)

Creating A Link Within Table Results?
I am using php and mysql. Does anyone have any pointers on how to include links within table results that point to additional information also pulled from the same db table?

I have some extensive info that I want to query. The intial results are going to be displayed in a simple wire frame html table layout. I would like each row to include a link that will take the reader to a more detailed version of the data for that particular row, and that more detailed data will of course come from the same db table. My problem is I don't know how to setup the code to create such link.

Display Query Results In A Table
$sql = "SELECT name FROM employee_tb;
$result_query = @mysql_query ($sql);// run the query
while ($row = mysql_fetch_array($result_query,MYSQL_NUM)){
$name = $row[0];}
// assuming i have 8 results

how can i display the results from this query in a table with 3 rows each column?

Displaying Array Results In Table
I'd like to display the results of an array in a table format with the Alias, Alias Destination & Username as column headings & the results underneath. PHP Code:

Putting Database Results In A 5x1 Table
I have a db of tens of thousands of entries. It's not too hard to pull
all the entries and build a table that displays them one at a time, but
in my case, that will be a huge waste of space. What I want to do is
put one record in each of the columns. I thinks it's possible to do
because I have seen very elaborate thumbnail gallery scripts to do this.
I just want to be able to put two or three strings of text from each
record into each row, and a checkbox. Here's sample of what I mean:

<table width="76%" border="1" cellspacing="1" cellpadding="1">
<tr>
<td>$record1Line1<br>$record1$Line2<br>
<input type="checkbox" name="$record1checkbox" value="checkbox"></td>
<td>$record2Line1<br>$record2$Line2<br>
<input type="checkbox" name="$record2checkbox" value="checkbox"></td>
<td>$record3Line1<br>$record3$Line2<br>
<input type="checkbox" name="$record3checkbox" value="checkbox"></td>
<td>$record4Line1<br>$record4$Line2<br>
<input type="checkbox" name="$record3checkbox" value="checkbox"></td>
<td>$record5Line1<br>$record5$Line2<br>
<input type="checkbox" name="$record5checkbox" value="checkbox"></td>
</tr>
</table>

I want to pull about 200 records at a time from the db and display as
described above. How can I tell php to take five records from my result
set and display above, then loop through the result set until I'm done?

Getting Data From A Table Based On The Results From Another?
This is the second part of my problems with a top ten list: I'm making a site for guitar effects. I'm trying to make a list of the top ten effects pedals, based on how many effects settings (patches) people have submitted for each pedal. There are two tables, pedals and patches:

I want it to select patches and find out which "pedal_id" is most common, then select that pedal ID from pedals, echo some tuff, then do the query again, except search for the SECOND most popular value, echo it, then the third, echo it, etc etc etc

Display Results Of Survey In A Table.
Is it possible to have them fill in a survey with about 10 form fields, then display
the results of everyone's survey in a table that can be sorted by different criteria
so new users to the site can instantly see which one of our services is better for them.

Results From Two Db Tables Into One Html Table?
I have a query below that gets its results and puts them in a table. The current results are from two fields in a db table. I am wondering if it is possible to create a second query to get results from a different db table and echo them out in the same html table as the ones already there? Code:

Display Results From Query In HTML-table
I'm working with PHP3 and Oracle 8. Can anyone tell me how to display the results from a query into my HTML-table?

Output Query Results To Html Table
I have a MySQL table "products" and I want to output product's data to html table as below:
- A products contain 4 fields: image, name, description, price.
- 4 products is nested in 1 html table 4 rows x 4 columns.

Code:

List Db Results Vertically In 2-column Table
i'd like to display mysql query results in a two column table, however, with the results displaying vertically starting in column 1 then continuing into column 2.

Loop To Print Formatted Table From Sql Results
I'm trying to create a 3 column table now, and each cell will contain info from a single record. All the records will be pulled from a mysql database.

What makes this a bit difficult is that I want the center column to be blank. Only the first and third columns will contain the results. I can't get the desired effect, and I'm not sure my loop logic is even correct. Does anyone know what's wrong with my code?
Code:

Printing Mysql_fetch_array Results In Multicolumn Table
I'm not sure if this qualifies as a mysql or a php question so I'm
asking in both groups.

I am pulling the results of a mysql query from my database and want to
print the results into a two column table. I know how to get the results
into a single column table just fine using:

while($row = mysql_fetch_array($result)) {
print "<table border=2><tr><th>" . $row[name];
print "<tr><td>";
print mysql_field_name($result, 0) . ": " . $row[ID]."<br>";
print mysql_field_name($result, 1) . ": " . $row[name]."<br>";
print mysql_field_name($result, 2) . ": " . $row[address]."<br>";
print mysql_field_name($result, 3) . ": " . $row[city]."<br>";
print mysql_field_name($result, 4) . ": " . $row[telephone]."<br>";
print "</td></tr>";
print "</table>
";
print "<br><br>";
}

and it works fine. But my efforts to get the reults into a two column
setup have become frustrating. My latest attempt was:

while($row = mysql_fetch_array($result)) {
print "<table border=2 width=ྖ%'>";
print "<tr>";
print "<td>";
print "<b>" . $row[name] . "</b><br>";
print mysql_field_name($result, 0) . ": " . $row[ID]."<br>";
print mysql_field_name($result, 1) . ": " . $row[name]."<br>";
print mysql_field_name($result, 2) . ": " . $row[address]."<br>";
print mysql_field_name($result, 3) . ": " . $row[city]."<br>";
print mysql_field_name($result, 4) . ": " . $row[telephone]."<br>";
print "</td>";
print "<td>";
print mysql_field_name($result, 0) . ": " . $row[ID]."<br>";
print mysql_field_name($result, 1) . ": " . $row[name]."<br>";
print mysql_field_name($result, 2) . ": " . $row[address]."<br>";
print mysql_field_name($result, 3) . ": " . $row[city]."<br>";
print mysql_field_name($result, 4) . ": " . $row[telephone]."<br>";
print "</td>";
print "</tr>";
print "</table>
";
print "<br><br>";
}

Formatting Mysql_fetch_array Results In HTML Table
This looks like a simple problem, but I just can't seem to wrap my
head around it.

I'm trying to build a table dynamically, based on a result set from
mysql. There will be 9 results for a full page, with 3 table rows of 3
table cells. One result per cell. But all of the pages won't be full.

Here's one of many ways I've tried to do it:

<html>
<head>
<title>test</title>
</head>
<body>
<table width="100%" border="1">
<?php

include_once('db.inc.php');

$query = "SELECT DISTINCT title FROM mytable LIMIT 9";
$result = mysql_query($query);

while ($row = mysql_fetch_array($result)) {
$title = $row['title'];
}

for ($i = 0; $i < 3; $i++) {
print ' <tr>' . "
";

for ($e = 0; $e < 3; $e++) {
print ' <td align="center">' . "
".
' title : ' . $title . "
".
' </td>' . "
";
}
print ' </tr>' . "
";
}

?>
</table>
</body>
</html>

This prints out 3 rows of three like I hoped, but $title is the same
in all 9 cells. I also have tried it with the for loops inside the
while loop, which prints out 9 rows of 9 cells 9 times!

Getting Results To Display In Two Columns Of A HTML Table
How can i get the following script to display in two columns of a HTML table? At the moment i have it displaying in one column. If there are 5 results from the DB then the left HTML table column will display the 1st, 3rd and 5th result. The right HTML table column will display the 2nd and 4th results. Code:

Displaying SELECT Results In A Formatted Table
I was wondering how to show a table with a max limit of 20 results per page. I have various data being pulled and its putting it in a table, but its displayed all in one row. I want to format it so its a 4 column/5 row table. With that I then want the page to generate a numbered list at the bottom that calculates how many pages it takes to show 20 items on each. Code:

Displaying SQL Query Results In 3 Column Table
I would like to be able to display the results of an sql query in an html table, but the results need to be displayed in three columns, for example:

Column 1 Column 2 Column 3
Result 1 Result 2 Result 3
Result 4 Result 5 Result 6
Result 7 Result 8 Result 9

I've tried to find the answers on the internet but i can't find anything that suits my needs!

Advice On Sorting Results (in A Table): Session Variables?
I have a table of query results (from PostgreSQL) that I am displaying using PHP. I would like to give the user the option to sort the results by clicking on one of the various table headings. I know that I can sort them at the time that I query them, but this would require a new database query each time. I am thinking of making the heading a hyperlink similar to the following:

<a href=myresultspage.php?sort=price>
//this would be the link to sort by the price collumn.

I have managed to make a simple sorting function, by putting the results into an array then sorting the array by the appropriate dimension of the array as indicated by the value of $sort. My question is this:

If I keep the results in an array, how can I pass the array when the user clicks on the hyperlink that tells which collumn to sort by? Do I need to use session variables? It doesn't seem like I can pass an array as a GET variable.

Grab Content From HTML Table And Place Into MySQL Table?
I am in the process of helping a co-worker upgrade some of his old static html pages into dynamic MySQL driven pages. He currently has a lot of pages with huge tables displaying data. Does anyone know if there is a script or class that can convert a table to a .sql file for upload?

Mysql Fetch_field Gets Table Alias, Not Real Table Name
After a SQL 'select .... from tablename alias'
the mysql_fetch_field function returns a value $result=>table which
will contain the alias, not the actual table name.
Is there a way to get the actual table name ?

I am running mysql 4.1 and php 4.4

Using PHP To Sort Data In MySQL Table 1 By Values In Table 2
I have a MySQL database with 2 tables in it. products and prices.. Products has a field in it called 'prodno'. Prices has a field in it called 'prodid'.

There is one of each item in products and each product has it's own unique 'prodno'

There are multiple instances of each product's pricing in prices.. One for each price.. So if a product had multiple prices depending on quantity it would have an entry for each price.. Example:

id = 1
prodid = 7001
qty = 300
price = 12.5

id = 2
prodid = 7001
qty = 400
price = 15.5

I was wondering if I could query the database getting info from the products table but sort it according to the prices in the prices table.

Kinda like doing a "SELECT * FROM products" ordering it by the lowest price value from each item..

This is all very confusing to me, and I'm the one writing it. Let's try one last time..

Query the database selecting * from products (I plan on using all the info in products) and ordering them by the lowest price for each item in the prices table. I've tried sorting the results of just a basic SELECT * FROM products using Javascript and PHP but with pagination in the results it makes it a bit hard.

JOIN Table Results W/MySQL

I have a query with a simple JOIN.For example:SELECT * FROM CompaniesINNER JOIN CompanyTypes ON Companies.CompanyID = CompanyTypes.CompanyIDThe query returns all fields from both Companies AND CompanyTypes. I wouldlike all fields from Companies only.

I have a query with a simple JOIN.

For example:

SELECT * FROM Companies
INNER JOIN CompanyTypes ON Companies.CompanyID = CompanyTypes.CompanyID

The query returns all fields from both Companies AND CompanyTypes. I would
like all fields from Companies only.