Kaupade otsing poes (tootekataloog)

kaubad:
id nimetus kaubagrupi_id hind 

kaubagrupid:
id grupinimi

nt

kaubagrupid

1 tellised
2 katusematerjal
3 vineer

kaubad

1 ahjutellis      1    8.20
2 fassaaditellis  1    7.50
3 bituumenrull    2  520.00

CREATE TABLE kaubagrupid(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  grupinimi VARCHAR(255)
);

INSERT INTO kaubagrupid(grupinimi) VALUES ('tellised');
INSERT INTO kaubagrupid(grupinimi) VALUES ('katusematerjal');

mysql> SELECT * FROM kaubagrupid;
+----+----------------+
| id | grupinimi      |
+----+----------------+
|  1 | tellised       |
|  2 | katusematerjal |
+----+----------------+
2 rows in set (0.00 sec)

CREATE TABLE kaubad(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  nimetus VARCHAR(255),
  kaubagrupi_id INT,
  hind DECIMAL(10, 2)
);

INSERT INTO kaubad (nimetus, kaubagrupi_id, hind) VALUES ('ahjutellis', 1, 8.20);
INSERT INTO kaubad (nimetus, kaubagrupi_id, hind) VALUES ('fassaaditellis', 1, 7.50);
INSERT INTO kaubad (nimetus, kaubagrupi_id, hind) VALUES ('bituumenrull', 2, 520);

mysql> SELECT * FROM kaubad;
+----+----------------+---------------+--------+
| id | nimetus        | kaubagrupi_id | hind   |
+----+----------------+---------------+--------+
|  1 | ahjutellis     |             1 |   8.20 |
|  2 | fassaaditellis |             1 |   7.50 |
|  3 | bituumenrull   |             2 | 520.00 |
+----+----------------+---------------+--------+

SELECT nimetus, grupinimi, hind
FROM kaubad, kaubagrupid
WHERE kaubad.kaubagrupi_id=kaubagrupid.id;


mysql> SELECT nimetus, grupinimi, hind
    -> FROM kaubad, kaubagrupid
    -> WHERE kaubad.kaubagrupi_id=kaubagrupid.id;
+----------------+----------------+--------+
| nimetus        | grupinimi      | hind   |
+----------------+----------------+--------+
| ahjutellis     | tellised       |   8.20 |
| fassaaditellis | tellised       |   7.50 |
| bituumenrull   | katusematerjal | 520.00 |
+----------------+----------------+--------+


mysql> SELECT nimetus, hind FROM kaubad WHERE hind<10.00;
+----------------+------+
| nimetus        | hind |
+----------------+------+
| ahjutellis     | 8.20 |
| fassaaditellis | 7.50 |
+----------------+------+

mysql> SELECT nimetus, hind FROM kaubad WHERE nimetus LIKE '%tellis%';
+----------------+------+
| nimetus        | hind |
+----------------+------+
| ahjutellis     | 8.20 |
| fassaaditellis | 7.50 |
+----------------+------+

SELECT nimetus, grupinimi, hind
FROM kaubad, kaubagrupid
WHERE kaubad.kaubagrupi_id=kaubagrupid.id
  AND nimetus LIKE '%tellis%';


mysql> SELECT nimetus, grupinimi, hind
    -> FROM kaubad, kaubagrupid
    -> WHERE kaubad.kaubagrupi_id=kaubagrupid.id
    ->   AND nimetus LIKE '%tellis%';
+----------------+-----------+------+
| nimetus        | grupinimi | hind |
+----------------+-----------+------+
| ahjutellis     | tellised  | 8.20 |
| fassaaditellis | tellised  | 7.50 |
+----------------+-----------+------+


Veateade: 
Column 'id' in field list is ambiguous



<?php
  $yhendus=new mysqli("localhost", "jaagup", "xxxxxx", "jaagup");

  function kysiKaupadeAndmed(){
    global $yhendus;
    $kask=$yhendus->prepare("SELECT kaubad.id, nimetus, grupinimi, hind
       FROM kaubad, kaubagrupid
       WHERE kaubad.kaubagrupi_id=kaubagrupid.id");
    //echo $yhendus->error;
    $kask->bind_result($id, $nimetus, $grupinimi, $hind);
    $kask->execute();
    $hoidla=array();
    while($kask->fetch()){
      $kaup=new stdClass();
      $kaup->id=$id;
      $kaup->nimetus=htmlspecialchars($nimetus);
      $kaup->grupinimi=htmlspecialchars($grupinimi);
      $kaup->hind=$hind;
      array_push($hoidla, $kaup);
    }
    return $hoidla;
  }
?>
<pre>
<?php
  print_r(kysiKaupadeAndmed());
?>
</pre>




Array
(
    [0] => stdClass Object
        (
            [id] => 1
            [nimetus] => ahjutellis
            [grupinimi] => tellised
            [hind] => 8.20
        )

    [1] => stdClass Object
        (
            [id] => 2
            [nimetus] => fassaaditellis
            [grupinimi] => tellised
            [hind] => 7.50
        )

    [2] => stdClass Object
        (
            [id] => 3
            [nimetus] => bituumenrull
            [grupinimi] => katusematerjal
            [hind] => 520.00
        )

)



<?php
  require("abifunktsioonid.php");
  $kaubad=kysiKaupadeAndmed();
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
      <title>Kaupade leht</title>
      <meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
  </head>
  <body>
     <table>
       <tr>
         <th>Nimetus</th>
         <th>Kaubagrupp</th>
         <th>Hind</th>
       </tr>
       <?php foreach($kaubad as $kaup): ?>
         <tr>
           <td><?=$kaup->nimetus ?></td>
           <td><?=$kaup->grupinimi ?></td>
           <td><?=$kaup->hind ?></td>
         </tr>
       <?php endforeach; ?>
     </table>
  </body>
</html>


<?php
  $yhendus=new mysqli("localhost", "jaagup", "xxxxxx", "jaagup");

  function kysiKaupadeAndmed(){
    global $yhendus;
    $kask=$yhendus->prepare("SELECT kaubad.id, nimetus, grupinimi, hind
       FROM kaubad, kaubagrupid
       WHERE kaubad.kaubagrupi_id=kaubagrupid.id");
    //echo $yhendus->error;
    $kask->bind_result($id, $nimetus, $grupinimi, $hind);
    $kask->execute();
    $hoidla=array();
    while($kask->fetch()){
      $kaup=new stdClass();
      $kaup->id=$id;
      $kaup->nimetus=htmlspecialchars($nimetus);
      $kaup->grupinimi=htmlspecialchars($grupinimi);
      $kaup->hind=$hind;
      array_push($hoidla, $kaup);
    }
    return $hoidla;
  }

  //---------------
  if( array_pop(explode("/", $_SERVER["PHP_SELF"]))=="abifunktsioonid.php"):
?>
<pre>
<?php
  print_r(kysiKaupadeAndmed());
?>
</pre>
<?php endif ?>