joining mysql tables with php

panreach

Up front I'd like to say forgive me for being a newbie. That's indeed why I'm asking for help.

I've two tables: event and venue

Their fields:
event: date, start, end, venueid

venue: venueid, name, street, city, directions/web

I want to list the events on a webpage so that it displays the rows as follows:

date,start,end,name,street,city,directions.

They have a common field which is the venueid.

I'm trying to read anything I can get my hands on but can't figure this one out. I need help. Thanks in advance for any help.

Anon

Wheres your problem...Creating the database or getting values fro the database and displaying it on the page??

RahuL

Troy wrote:

Up front I'd like to say forgive me for being a newbie. That's indeed why I'm asking for help.

I've two tables: event and venue

Their fields:
event: date, start, end, venueid

venue: venueid, name, street, city, directions/web

I want to list the events on a webpage so that it displays the rows as follows:

date,start,end,name,street,city,directions.

They have a common field which is the venueid.

I'm trying to read anything I can get my hands on but can't figure this one out. I need help. Thanks in advance for any help.

indrajit_m

Hello,
I can give you a code that displays a MySQL table contents in HTML page. May be you can modify this according to your requirement. In this code "myworks" is the database that I created in MySQL. "Class is the table name with the following attributes.
id integer(3)
name varchar(20).
Goodluck.

<?php
$user = "root";
$pass = "root";
$db = "myworks";

$link = mysql_connect("localhost",$user,$pass);
if (! $link)
die("Could not connect to MySQL");
mysql_select_db( $db,$link)
or die("Could not open $db: ".mysql_error());
$result = mysql_query("select * from class");
$num_rows = mysql_num_rows($result);
#print "There are currently $num_rows rows in the table<P>";
print "<table border=1 width=44% cellspacing=0 cellpadding=0>\n";
while($a_row = mysql_fetch_row($result))
{
print "<tr>\n";
foreach($a_row as $field)
print "\t<td width = 50%>&nbsp$field</td>\n";
print "</tr>\n";
}
print"</table>\n";
mysql_close( $link);
?>

panreach

I built a form that inserts a venue into the venue table. Then I built a form that inserts an event into the event table.

In the event form I can select a venue from the venue table so that the new event contains the venueid from the venue.

I want to be able to select the event table, but at the same time display the venue and its fields next to each event.

so if event 1,3 both use venue a. and event 2,4 both use venue b. I want it to look something like:

event1: date, start, end; venue a: name, street, city, directions/website.

event2: date, start, end; venue b: name, street, city, directions/website.

event3: date, start, end; venue a: name, street, city, directions/website.

event4: date, start, end; venue b: name, street, city, directions/website.

I want the venue table separate for the simple reason the most venues around here like to change their name frequently. I don't want to have to update a million entrys with the correct name. Isn't this the reason for having tables in a database?

panreach

Your code is very very slick! but I need to get data from two tables. If the event table has the unique venueid of a venue in the venue table, that venue needs to show up next to the event. Not the just the venueid itself, but all of its fields ie; name, street, city, directions/webpage.

Anon

Hi Troy,
from the top of my head (Being a newby myself..) try this:
first the normal select statement:

... select date, venue, street, ..., from table1, table2 ....

Then the connection between the tables (which is waht you are looking for):

... WHERE table1.venueid = table2.venueid ...

I think this last line should solve your queste. If this doesn't work: Let me know in two-three hours: I'll be home from work then, and I can check some code I use at home.

Good luck,

panreach

I've tryed this, but it won't work.

<?
@ $db = mysql_pconnect("localhost", "user", "password");
if (!db)
{
echo "Unable to connect to that darn server. Go back and try again.";
exit;
}
mysql_select_db("calendar", $db);

$result = mysql_query("select venue.name, venue.street, venue.City, venue.directions, event.date, event.start, event.sm, event.end, from venue, event where event.venueid = venue.venueid");

$num_results = mysql_num_rows($result);

for ($i=0; $i <$num_results; $i++)
{
$row = mysql_fetch_object($result);
echo stripslashes($row->date);
echo stripslashes($row->start);
echo stripslashes($row->sm);
echo stripslashes($row->end);
echo stripslashes($row->em);
echo stripslashes($row->name);
echo stripslashes($row->street);
echo stripslashes($row->City);
echo stripslashes($row->directions);
}
?>

panreach

I figured it out. Right join ON instead of WHERE.

$result = mysql_query("select venue.name, venue.street, venue.City, venue.directions, event.date, event.start, event.sm, event.end from venue right join event on event.venueid = venue.venueid");