PHP query with two table

Question

In reference to this topic.

My problem is that I insert into a table using PHP 5 columns, but one of them is a foreign key to another table. So, I would see the value and not the link directory.
For example Orders.Name contain values "1", "2", "3", "4" etc... they are contained in tables "Clients" on columns with ID "1" the "Name" is "Jack", ID "2" the "Name" is "Mark", ID "3" the "Name" is "Frank"... and so on. So, I would to see "Jack", not "1".

Example:
Database name: DinamicoWeb
Table name: Orders
Fields name: Id Ord, Ord Date, Name, Price, Total
Second Table name: Clients
Fields name: Id Client, Name, Cell, City, Address

My actual result:

Id Ord   Ord Date    Name    Price    Totale
   1      14/2/99     1       189       345

Id Client   Name       Cell        City       Street
    1       Jack      23445456    Italy      Road nr 2

My wish result:

Id Ord   Ord Date    Name    Price    Totale
   1      14/2/99    Jack     189       345

So, this is my code.

config.php

<?php
define ('DBNAME',"./DinamicoWeb.mdb"); // Database name
define ('DBTBL',"Orders"); // Table name 1
define ('PKNAME',"Id Ord"); // Primary Key
define ('PKCOL',0); // Position Primary Key
define ('LINKPK',true); // PK link for edit/delete
?>

test.php

<?php
require_once("config.php");

$cn = new COM("ADODB.Connection");
$cnStr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=".
            realpath(DBNAME).";";
$cn->open($cnStr);
$rs = $cn->execute("SELECT [Id Ord] AS [ID], [Ord Date] AS [Date], [Name] AS [Name], [Price] AS [Price], [Total] AS [TOTAL] FROM [Orders]");
$numFields = $rs->Fields->count;

// Print HTML
echo '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
        "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">';
echo '<html xmlns="http://www.w3.org/1999/xhtml">';
echo '<head>';
echo '<meta http-equiv="Content-Type" 
     content="text/html; charset=utf-8" />';
echo '<title>Gestione degli '.DBTBL.'</title>';
echo '<link href="styles.css" rel="stylesheet" type="text/css" />';
echo '<link rel="stylesheet" href="css/bootstrap.css">';
echo '<link rel="stylesheet" href="css/footable.bootstrap.css">';
echo '<link rel="stylesheet" href="css/footable.bootstrap.min.css">';
echo '<link rel="stylesheet" href="css/footable.core.bootstrap.min.css">';
echo '</head><body>';
echo '<h1>GESTIONE '.DBTBL.'</h1>';
// Elenca records -----
//echo ("<div class='table-responsive'>");
echo ("<table class='datatable table tabella_reponsive ui-responsive' summary='Prova dati con MS Access'>");
echo("<caption>Tabella ".DBTBL."</caption>\n");
echo("<thead><tr>\n");
for ($i=0;$i<$numFields;$i++){
    echo("<th scope='col'>");
    echo $rs->Fields($i)->name;
    echo("</th>\n");
}
echo("</tr></thead>\n");
echo("<tbody>");

$alt = false;
while (!$rs->EOF)
{
    echo("<tr>");
    for ($i=0;$i<$numFields;$i++){
      $altClass = $alt ? " class='alt'" : "";
      if (LINKPK && $i==PKCOL){
        echo "<td".$altClass."><a href='?id=".$rs->Fields($i)->value
              ."'>".$rs->Fields($i)->value."</a></td>\n";
      }
      else{
        echo "<td".$altClass.">".$rs->Fields($i)->value."</td>\n";
      }
    }
    echo("</tr>\n");    
    $rs->MoveNext();
    $alt = !$alt;
}
echo("</tbody>");
echo("</table>\n");
echo("</div>");
echo '<script src="js/footable.js"></script>';
echo '<script src="js/footable.min.js"></script>';
}
echo '</body></html>';
$rs->Close();
$cn->Close();
?>

Thanks!

UPDATE:
I have relationship between Client and Order table.

This is my query on Access. (Different name and different request)

SELECT DISTINCT Ordini.[Id Ord], Ordini.[Tipo Ord] AS Tipo, Ordini.[N Ord] AS Numero, Ordini.[Data Ord] AS Data, Ordini.Anno, Anagrafica.CodAnag AS Codice, Ordini.[Ragione sociale], IIf([stato]=0,"inserito",IIf([stato]=1,"stampato","Bloccato")) AS [Stato ord], Ordini.[Data consegna] AS Consegna, Ordini.ValidoFinoAl AS Validità, [tabella pagamenti].Descrizione AS Pagamento, Ordini.Rif1 AS [Ns Riferimenti], Ordini.Rif2 AS [Vs Riferimenti], IIf(Not IsNull([idsped]),[anagrafica spedizioni].[Ragione Sociale] & " " & [anagrafica spedizioni].Indirizzo & " " & [anagrafica spedizioni].Località,IIf(Not IsNull(anagrafica_1.[id anag]),anagrafica_1.[Ragione Sociale] & " " & anagrafica_1.Indirizzo & " " & anagrafica_1.Località)) AS Destinazione, Ordini.TotImp, Ordini.TotNI, Ordini.Cambio, Temp_Ordini_Interroga.ApertoEuro, Temp_Ordini_Interroga.TotaleEuro, Ordini.Sospeso
FROM ((((Ordini INNER JOIN Anagrafica ON Ordini.[Id anag] = Anagrafica.[Id anag]) INNER JOIN [tabella pagamenti] ON Ordini.[Id pagamento] = [tabella pagamenti].[Id pagamento]) LEFT JOIN [anagrafica spedizioni] ON (Ordini.DestSped = [anagrafica spedizioni].CodSped) AND (Ordini.Dest = [anagrafica spedizioni].[Id anag])) LEFT JOIN Anagrafica AS Anagrafica_1 ON Ordini.Dest = Anagrafica_1.[Id anag]) INNER JOIN Temp_Ordini_Interroga ON Ordini.[Id Ord] = Temp_Ordini_Interroga.IdOrd
ORDER BY Ordini.Anno DESC , Ordini.[Data Ord] DESC , Ordini.[N Ord] DESC;

What you can do is use JOIN on your query and the relationship would be Orders.[Name] = Clients.[Id Ord] — L. Herrera, Nov 03 '16 at 08:14

score 0 · Answer 1 · answered Nov 03 '16 at 08:15

0

I don't know if i understand your question, but i think you should try to change your query for example:

SELECT [Id Ord] AS [ID], [Ord Date] AS [Date], [Clients.name] AS [Name], [Price] AS [Price], [Total] AS [TOTAL] FROM [Orders] LEFT JOIN [Clients] ON [Clients.id]=[Orders.name]

answered Nov 03 '16 at 08:15

th3fr33man

60
1
5

1

I try this solution, but doesn't work. Message: `Fatal error: Uncaught exception 'com_exception' with message 'Source: Microsoft OLE DB Provider for ODBC Drivers
Description: [Microsoft][Driver ODBC Microsoft Access] Parentesi non valide nel nome '[Clients.Id]'.` – Nov 03 '16 at 08:39
Maybe something like this: `code`Clients.[Id Client] = Orders.[name]`code` – th3fr33man Nov 03 '16 at 08:58
Maybe something like this: `Clients.[Id Client] = Orders.[name]` – th3fr33man Nov 03 '16 at 09:00
And in the selected field `Clients.[name] AS [name]` – th3fr33man Nov 03 '16 at 09:01
I change the code, now there aren't error on editor and final page. But it doesn't working, I don't know the cause. – Nov 03 '16 at 09:12
is the variable `$numFields` > 0, echo the var to see the value if there is no value maybe the call of adodb is not correct. Then look at this topic (http://stackoverflow.com/questions/2245140/how-to-get-field-names-using-php-adodb) – th3fr33man Nov 03 '16 at 09:47

score 0 · Answer 2 · edited Nov 11 '16 at 22:36

0

Try this

SELECT 
    [Orders.Id Ord] AS [ID], [Orders.Ord Date] AS [Date], 
    [Clients.Name] AS [Name], [Orders.Price] AS [Price], 
    [Orders.Total] AS [TOTAL] 
FROM 
    [Orders] 
JOIN
    Clients ON Orders.name = clients.client id;

NOTE: remove spaces in column's name.

edited Nov 11 '16 at 22:36

marc_s

732,580
175
1,330
1,459

answered Nov 03 '16 at 08:16

jai dutt

780
6
13

1

I try this solution, but doesn't work. Message: `Fatal error: Uncaught exception 'com_exception' with message 'Source: Microsoft OLE DB Provider for ODBC Drivers
Description: [Microsoft][Driver ODBC Microsoft Access] Parentesi non valide nel nome '[Clients.Id]'.` – Nov 03 '16 at 08:40
Not work. The right sintax is `Orders.[Name]` like suggest @th3fr33man , but if i change both code don't work. – Nov 03 '16 at 09:18

score 0 · Answer 3 · edited Nov 03 '16 at 09:23

0

Do not repeat same name with as in sql. Also do not keep space when assign name for column. Use underscore (_) for word separation.

SELECT [Id Ord] AS [ID], [Ord Date] AS [Date], [Name] , [Price], [Total] 
FROM [Orders],[Clients]
WHERE [Orders.Name]=[Clients.Id];

edited Nov 03 '16 at 09:23

answered Nov 03 '16 at 08:20

Chandana Kumara

2,485
1
22
25

1

I try this solution, but doesn't work. Message: `Fatal error: Uncaught exception 'com_exception' with message 'Source: Microsoft OLE DB Provider for ODBC Drivers
Description: [Microsoft][Driver ODBC Microsoft Access] Parentesi non valide nel nome '[Clients.Id]'.` – Nov 03 '16 at 08:40
You can't use this way '[Clients.Id]'. Use this as ``Clients.Id`` – Chandana Kumara Nov 03 '16 at 08:48
I already try. Message: `Fatal error: Uncaught exception 'com_exception' with message 'Source: Microsoft OLE DB Provider for ODBC Drivers
Description: [Microsoft][Driver ODBC Microsoft Access] Syntax error (missing operator) in query 'Clients.Id=Orders.Name'` – Nov 03 '16 at 08:52
What is this 'Clients.Id=Orders.Id'? – Chandana Kumara Nov 03 '16 at 08:53
I did a mistake when copied the error, now i edited the last comment, sorry. I can't use `Clients.'Id Client';` – Nov 03 '16 at 08:55
ah . You can add (') this as above. for that use this as Clients.Id=Orders.Name or [Clients.Id]=[Orders.Name]. unless replace bracket with (`) – Chandana Kumara Nov 03 '16 at 08:57
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/127305/discussion-between-chandana-kumara-and-m-bagiak). – Chandana Kumara Nov 03 '16 at 15:28

score 0 · Answer 4 · answered Nov 03 '16 at 08:25

you have to use join in your sql request.

example :

Select orders.idOrd, orders.ordDate, client.name, orders.price, orders.totale 
From orders
join client on client.idClient = orders.Name

you can also use alias for table as :

Select o.idOrd, o.ordDate, c.name, o.price, o. totale
From orders o
join client c on c.idClient = o.Name

This request will select the corresponding name from the client table in c.name column.

You should rename the "name" column in the orders table as "ClientId" to be coherent.

This doesn't work if I write "Orders.Id ord" (with space). How can I rename all columns on PHP (not on DB Access)? — , Nov 03 '16 at 08:45

PHP query with two table

4 Answers4