How do i get a fields contents to link to another tables entry

[grrraaagh]

I wish to have two tables in a database.

 

The rows/records on the second table will consist of a column for ID and a text (blob???) column containing a football match report.

 

The main table will have in each row fields for ID, competition, round, venue, date, home team ranking, away team ranking,  home team, away team, result, and then ideally the final field will be a link to the match report. This should when clicked open up in either a separate window/floating window or frame.

 

Basically, if i enter a search for a particular team, the main table will show all the matches they have played in on a row by row basis. But, i would only want a match report to display when i click on the link.

 

Please can anyone let me know how i can achieve this?. I've Googled away relentlessly after exhausting all efforts of my own. I would have thought this is far from a rare function to be needed.

 

Many thanks in advance of assistance.

[js_280]

As far as the MySQL side...

 

'SELECT m.comp_ID AS id, m.competition AS comp, m.round AS round, m.venue AS venue, m.date AS date, m.home_ranking AS h_rank, m.away_ranking AS a_rank, m.home_team AS h_team, m.away_team AS a_team, m.result AS result, s.report AS report FROM main AS m LEFT JOIN second_table AS s ON s.comp_ID = m.comp_ID WHERE m.comp_ID = " place your search criteria here "';

 

Substitute the field names and table names with yours...

[grrraaagh]

As far as the MySQL side...

 

'SELECT m.comp_ID AS id, m.competition AS comp, m.round AS round, m.venue AS venue, m.date AS date, m.home_ranking AS h_rank, m.away_ranking AS a_rank, m.home_team AS h_team, m.away_team AS a_team, m.result AS result, s.report AS report FROM main AS m LEFT JOIN second_table AS s ON s.comp_ID = m.comp_ID WHERE m.comp_ID = " place your search criteria here "';

 

Substitute the field names and table names with yours...

 

I've cross posted whilst editing my original query to make it clearer.

 

I'll try and get my head around your suggestion but i'm a little lost on the use of so many AS's. I guess i'm about to put a lot of study time into LEFT JOIN etc.

[grrraaagh]

A further complication/hurdle to cross is that, i would be adding the report contents at a later date than the initial fields. Therefore, how do i ensure that the id for the record in both tables is the same?

 

By that i mean, i need to trigger the creation of the report field at the time of entering all the original (other) fields. This report field will be empty until a day or so later.

 

I can't figure out how to assign an id (auto-increment) for a record to cover two different tables. I can't create a manual id every time i make a new record. It would be too time-consuming, so i need an auto-increment to do it automatically.

 

My key task - to me - seems (wrongly???) to enable a (HTML?) link to my second tables entries. This second tables entries would have the same id for the match record in the main table.

 

During use of my database (via a web page) i would search for a team's games. I would then have on display the venue, date score etc. At the end of each of the displayed rows there would be a clickable link that will bring up in a separate window or frame the match report.

 

 

[grrraaagh]

I've made - to me - some amazing progress on this problem. I've now just two things to sort out, one of them is possibly relatively easy to sort. However, i still need help on one matter.

 

Is there any way of automatically inserting the auto-incremented id into both tables at the same time?. By that i mean, when i insert a new record into the main table and enter data into the various fields, can i get it to automatically generate a (initially empty) record with the same id into the report tables.

 

To clarify further; if my new record in the main table automatically generates the auto-incremented id of '216', can i get it to create a record in the other table with the same id of '216' (the contents of the field in this table will initially be empty and added to a day later) ?.

 

Hopefully, someone can please assist with this. I have slaved away for hours in getting the 'joins' to work (for now) to a certain degree.

[grrraaagh]

The solution to the problem posed lay in LAST_INSERT_ID( ). I'll explain further as soon as possible, i'm finished for the night.

 

I've still got a a couple of things to do with general interacting with the db via a web page.

 

By the way, i hope others will eventually be able to benefit from my talking to myself throughout this thread 

[grrraaagh]

I realise it's a lame cowardly way to accede defeat, but i need to get this database working very soon now.

 

I had come so close to sorting the matter out. However, my brain has bled so heavily that, i am now so punch drunk, i 'm losing grasp of basic syntax and coding.I have designed really interactive databases in the past where i can search for a term which throws up several records that contain it. I can then (all via a web page interface) update/edit/replace/insert data to my hearts content.I have engineered 'live' editing windows and all sorts of goodies.They are far better to use than the ones provided by my website host. They do more, clearer and faster than their interface. I can insert multiple records and alter swathes of data at a single stroke. But, i'm so frustrated and bewildered trying to sort out this 'simple' task that i'm totally at sea with the basics. It's not helped by the fact i was away from, messing about with, coding for quite a while. Just as i was starting to make sense of what i had self-taught myself.

 

So ! in order that i can find an easier solution to the problem, i'll put all data in a single BIG table - forget the demonic Join's and Last ID() etc.

 

In an effort to ellicit some help, i will strip the field down to a minimum for example purposes.

 

Here goes, please someone help if they can.

 

I have a table that has the following fields Home_Team, Away_Team, Result, Report.

 

When i enter a search for a team in the web page interface i want it to produce rows containing only the Home_Team, Away_Team & Result fields/columns

 

At the end of each row/record i further wish to have displayed a submit button. Upon pressing this, it will open in a new window the contents of the Report field for the match referred to in that same row. I see this done all over the web, virtually on every sports site if you click on the result of a match it brings up the match report. To make things as simple as possible, i am no longer keeping the reports in a separate table.

 

I have no problem scripting the submit button to display ALL reports upon clicking it. However, i'm so dizzy, i've lost all track of how (and where) to phrase the mysql statement so that it only selects rows with conditions attached. I can't for the life of me figure out where what (mysql/php/html statements) go where in relation to the DB fields,form fields and web page scripts.

 

Many thanks in advance of help.

 

 

[grrraaagh]

[code]My basic codes are as follows.

To select a team i enter the name, or for speed the first few letters (cze is less laborious that czechoslovakia), into a form interface on a web page.

All quite simple with;
[code]<form action="getgames.php" method="get"><input type="submit" name="submit" value="SELECT"></form>

 

This targets the following script;

 

<?php
$conn=@mysql_connect("localhost", "database", "password") or die("could not connect");
$rs=@mysql_select_db("database_name", $conn) or die("could not connect to database");

$sql=stripslashes ("select match_id, home_team, away_team, result, report FROM worldcup WHERE home_team = '%$tm%' OR away_team = '%$tm%'");

$rs=mysql_query($sql, $conn) or die("could not execute query because ".mysql_error());
$list="<table border=\"1\" cellpadding=\"2\">";
$list.="<tr><td><center><b></b></center></td></tr>";
$list.="<tr><td></td></tr>";
while($row=mysql_fetch_array($rs))
{
$list.="<tr>";
$list.="<td bgcolor=\"#ffcc99\"><center><b><u>".$row["match_id"]."</u></b></center></td>";
$list.="<td bgcolor=\"#ffcc99\"><center><b><u>".$row["home_team"]."</u></b></center></td>";
$list.="<td bgcolor=\"#ffcc99\"><center><b><u>".$row["away_team"]."</u></b></center></td>";
$list.="<td bgcolor=\"#ffcc99\"><center><b><u>".$row["result"]."</u></b></center></td>";
$list.="<td bgcolor=\"#ffcc99\"><center><b><u>".$row["report"]."</u></b></center></td>";
$list.="</tr>";
}
$list.="</table>";
echo($list);
?>

 

This will then produce a table with rows containing the data eg.;

 

1267 England  Germany 2-0 Report

1389 Russia    England  1-3 Report

1897 England  Scotland 3-2 Report

 

When finished in the future i will have the id number 'hidden'. When the table is initially displayed the last cell in each row just shows the word 'Report'. Once clicked on - in a separate window - it would bring up the detailed match report.I am stuck on the placement and syntax of the coding required to do the aforementioned task.

 

At the moment, in every row of my database in the field 'report', I have been trying various forms of syntax. I have only succeeded in getting a code which brings up ALL reports available in the DB, as opposed to the selected search parameters.

I've got the basic operating of the 'Report' button working in that it will 'action' on a script and get the MySql statement to operate.

 

Therefore, is the sticking point simply my wording of the MySql syntax - crucially - the reference of the rows id?. In the script below i have put question marks at the point where i think it's all going wrong.

 

This is the start of the script that the 'Report' button utilises, it attempts to display the previously undisplayed field of 'match_report' (this i have assigned as mediumtext, whether text will suffice i'm not sure);

 

<?php
$conn=@mysql_connect("localhost", "database", "password") or die("could not connect");
$rs=@mysql_select_db("database_name", $conn) or die("could not connect to database");

$sql=stripslashes ("SELECT match_report FROM worldcup WHERE ?????");

$rs=mysql_query($sql, $conn) or die("could not execute query because ".mysql_error());
.......
?>

 

Hopefully, the above makes it a little clearer that the previously prevailing mud.[/code][/code]