ouf newbe Column: 'nom' in field list is ambiguous

[Québec]

Hi,

The column nom is declare in all thee tables as INDEX

The idea as you gest is to print everything from all tables related to a
name. (nom translate name)


mysql_select_db("pierredata");

$res = mysql_query('SELECT nom, commentaire FROM cours,
peinture_plein_air, commentaires WHERE cours.nom = peinture_plein_air.nom
and peinture_plein_air.nom = commentaires.nom;');

if (!$res) {
echo "<p>"."Impossible ($res) in base : " . mysql_error();
exit();
}

if (mysql_num_rows($res) == 0) {
echo "<p>"."Nothing found.";
exit();
}

Jean

[Janwillem Borleffs]

Québec wrote:

> $res = mysql_query('SELECT nom, commentaire FROM cours,
> peinture_plein_air, commentaires WHERE cours.nom =
> peinture_plein_air.nom and peinture_plein_air.nom =
> commentaires.nom;');
>

You need to apply the AS clause to create aliasses, example:

SELECT crs.nom, ppa.nom, com.nom FROM cours AS crs,
peinture_plein_air AS ppa, commentaires AS com WHERE crs.nom =
ppa.nom and ppa.nom = com.nom;

Also, have a look at INNER JOINS:

[url]http://dev.mysql.com/doc/mysql/en/JOIN.html[/url]


JW

[Andy]

"Québec" <Once@WasEno.ugh> wrote in message
news:EgJ0d.85206$FX3.1205899@weber.videotron.net.. .

> Hi,
>
> The column nom is declare in all thee tables as INDEX
>
> The idea as you gest is to print everything from all tables related to a
> name. (nom translate name)
>
>
> mysql_select_db("pierredata");
>
> $res = mysql_query('SELECT nom, commentaire FROM cours,
> peinture_plein_air, commentaires WHERE cours.nom = peinture_plein_air.nom
> and peinture_plein_air.nom = commentaires.nom;');
>
> if (!$res) {
> echo "<p>"."Impossible ($res) in base : " . mysql_error();
> exit();
> }
>
> if (mysql_num_rows($res) == 0) {
> echo "<p>"."Nothing found.";
> exit();
> }
>
> Jean
>
>

You need to specify which table "nom" is to come from in your SELECT

e.g. SELECT cours.nom

[Québec]

Ok
Now I have

$res = mysql_query('SELECT etudiants.nom, commentaires.nom FROM
etudiants, commentaires WHERE etudiants.nom = commentaires.nom;');

while($row=mysql_fetch_array($res)){
$line++;
print $line.'<br>';
}

It prints 1 ??????????????????


--------------------------------------
In the tables:
nom refers to Jean Pierre Daviau
====== comentaire========== first column
('Jean Pierre Daviau', 'dddd@videotron.ca', 'Bonjour', 'Bonjour',
'2004-09-11');
====== etudiant==========third column
('dddd@videotron.ca', 'c#nC01rfgN', 'Jean Pierre Daviau', 'moi', 'gggg',
'5145555522', '2004-09-09');
================

CREATE TABLE `etudiants` (
`courriel` char(40) NOT NULL default '',
`passe` char(15) NOT NULL default '',
`nom` char(60) NOT NULL default '',
`image` char(20) NOT NULL default '',
`adresse` char(80) NOT NULL default '',
`tel` char(11) NOT NULL default '',
`date` date NOT NULL default '0000-00-00'
) TYPE=MyISAM PACK_KEYS=1;

CREATE TABLE `commentaires` (
`nom` varchar(50) NOT NULL default '',
`courriel` varchar(50) NOT NULL default '',
`sujet` varchar(50) NOT NULL default '',
`commentaire` text NOT NULL,
`date` date NOT NULL default '0000-00-00',
KEY `nom` (`nom`)
) TYPE=MyISAM;
Jean Pierre

[Andy]

"Québec" <Once@WasEno.ugh> wrote in message
news:hp%0d.13564$Qa2.185145@weber.videotron.net...

> Ok
> Now I have
>
> $res = mysql_query('SELECT etudiants.nom, commentaires.nom FROM
> etudiants, commentaires WHERE etudiants.nom = commentaires.nom;');
>
> while($row=mysql_fetch_array($res)){
> $line++;
> print $line.'<br>';
> }
>
> It prints 1 ??????????????????
>
>
> --------------------------------------
> In the tables:
> nom refers to Jean Pierre Daviau
> ====== comentaire========== first column
> ('Jean Pierre Daviau', 'dddd@videotron.ca', 'Bonjour', 'Bonjour',
> '2004-09-11');
> ====== etudiant==========third column
> ('dddd@videotron.ca', 'c#nC01rfgN', 'Jean Pierre Daviau', 'moi', 'gggg',
> '5145555522', '2004-09-09');
> ================
>
> CREATE TABLE `etudiants` (
> `courriel` char(40) NOT NULL default '',
> `passe` char(15) NOT NULL default '',
> `nom` char(60) NOT NULL default '',
> `image` char(20) NOT NULL default '',
> `adresse` char(80) NOT NULL default '',
> `tel` char(11) NOT NULL default '',
> `date` date NOT NULL default '0000-00-00'
> ) TYPE=MyISAM PACK_KEYS=1;
>
> CREATE TABLE `commentaires` (
> `nom` varchar(50) NOT NULL default '',
> `courriel` varchar(50) NOT NULL default '',
> `sujet` varchar(50) NOT NULL default '',
> `commentaire` text NOT NULL,
> `date` date NOT NULL default '0000-00-00',
> KEY `nom` (`nom`)
> ) TYPE=MyISAM;
> Jean Pierre
>
>

Well that's correct isn't it ? You're printing out a counter (line) which
you increment for each match of "nom" betweem the tables etudiants
and commentaires. From the name sample you show above then there
is one match so "line" gets set to 1.

[Québec]

Thank you very much that works!
Jean
===============
$result = mysql_query('SELECT etudiants.nom, etudiants.courriel,
etudiants.passe, commentaires.nom, commentaires.sujet,
commentaires.commentaire FROM etudiants, commentaires WHERE etudiants.nom
= commentaires.nom;');
$line=0;
if (!$result) {
echo "<p>"."Impossible d'exécuter la requête ($result) dans la base
: " . mysql_error();
exit();
}

if (mysql_num_rows($result) == 0) {
echo "<p>"."Aucune ligne trouvées, rien à afficher.";
exit();
}
$num_results = mysql_num_rows($result);
echo "<p>Number of passe found: ".$num_results."</p>";

for ($i=0; $i <$num_results; $i++)
{
$row = mysql_fetch_array($result);
echo "<p><strong>".($i+1).". nom: ";
echo stripslashes($row["nom"]);
echo "</strong><br>courriel: ";
echo stripslashes($row["courriel"]);
echo "<br>passe: ";
echo stripslashes($row["passe"]);
echo "<br>sujet: ";
echo stripslashes($row["sujet"]);
echo "<br>commentaire: ";
echo stripslashes($row["commentaire"]);
echo "</p>";