Member Avatar for filipgothic

I have this database and I need to display data from koncerti (koncertnaziv,karte,mesto,datum) based on what grupanaziv is sleceted before, and grupanaziv is in different table called grupe, grupa_id from koncerti has same id as id from grupe, so that is connected in that way,

so I need to match grupanaziv that user choosed as option and its id from grupe is same as grupa_id from koncerti, and display data from koncerti for that id, I need help with that

I am thinking of something like this

Select * From koncerti WHERE grupa_id (part which I dont know) MATCHES id FROM grupe (for previously selected grupanaziv) (and display koncertnaziv,mesto,datum,karte for that id FROM koncerti)

//Tabela Sections
$sql = "CREATE TABLE grupe
(
ID int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(ID),
grupanaziv varchar(64)
)";
mysql_query($sql,$con);
echo "Table grupe CREATED!<br>";
//Tabela Subjects
$sql = "CREATE TABLE koncerti
(
ID int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(ID),
koncertnaziv varchar(64),
karte int(10),
mesto varchar(64),
datum DATE,
grupa_id int(6)
)";
mysql_query($sql,$con);
echo "Table koncerti CREATED!<br>";

here is code where user have to choose option

 <p align="center"><font color="#0000FF">Pregled grupa</font></p>

<p align="center">Grupa kojoj koncert pripada: 
    <select name="grupa_select" id="grupa_select">
      <?php
    while($row = mysql_fetch_array($sql))
    {
    echo "<option>".$row['grupanaziv']."</option>";
    }
    ?>
    </select>
  </p>
  <p align="center">
    <input type="submit" name="pregled_grupe" id="pregled_grupe" value="- Izaberi grupu-" />
  </p>
  <p>
    <label></label>
  </p>

thanks in advance

Edited by filipgothic
  • 4 Contributors
  • 8 Replies
  • 203 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by filipgothic
Member Avatar for filipgothic

I am still working on same problem,

I need to do mysql command that will display all concerts and other data like (date,place,number of sold tickets) from table called concerts but thing is when new concert is added, it has same id as bandname from other table called bands, here is table content and inserting code

//Table bands
$sql = "CREATE TABLE bands
(
ID int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(ID),
bandname varchar(64)
)";
mysql_query($sql,$con);
echo "Table bands CREATED!<br>";
//Tabela concerts
$sql = "CREATE TABLE concerts
(
ID int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(ID),
concertname varchar(64),
tickets int(10),
place varchar(64),
date DATE,
band_id int(6)
)";
mysql_query($sql,$con);
echo "Table concerts CREATED!<br>";




//Creating new bands and concerts

<body>
<p align="center"><font color="#000000"><strong>Application administration</strong></font></p>
<form id="form1" name="form1" method="post" action="">

    <br>
  </p>
  <hr />
  <p align="center"><font color="#0000FF">Band registration</font></p>
  <p align="center">Inserting new band: 
    <input type="text" name="band_new" id="band_new" />
  </p>
  <p align="center">Save band:
    <input type="submit" name="save_band" id="save_band" value="- Save band -" />
  </p>
  <p>&nbsp;</p>
  <p>
    <label></label>
  </p>
  </p><br>

  <?php
  if (isset($_POST['save_band'])){
  $band_new = $_POST['band_new'];
  if (empty($band_new)){
  die ("ERROR: You did not insert band name!");
  }
  $con = mysql_connect($hostname,$db_username,$db_password);
    if (!$con)
    {
        die('Could not connect: ' . mysql_error());
    }
    mysql_select_db($db_name, $con);
    $sql = "insert into bands values (NULL,'".$band_new."')";
    if (!mysql_query($sql,$con))
    {
        die('Error: ' . mysql_error());
    }
    echo "Data inserted into database!";
  }
  ?>


  <br>
  <hr />
   <p align="center"><font color="#0000FF">Concert registration</font></p>
  <p align="center">Concert name: 
    <input type="text" name="enter_concert" id="enter_concert" />
  </p>
  <p align="center"><font color="#0000FF">Number of sold tickets</font></p>
  <p align="center">Number: 
    <input type="text" name="enter_tickets" id="enter_tickets" />
  </p>

   <p align="center"><font color="#0000FF">Place</font></p>
  <p align="center">Place: 
    <input type="text" name="enter_place" id="enter_place" />
  </p>

  <p align="center"><font color="#0000FF">Date</font></p>
  <p align="center">Date: 
    <input type="text" name="enter_date" id="enter_date" />
  </p>

  <p align="center">Which band concert belong: 
    <select name="band_select" id="band_select">
      <?php
    while($row = mysql_fetch_array($sql_2))
    {
    echo "<option>".$row['bandname']."</option>";
    }
    ?>
    </select>
  </p>
  <p align="center">
    <input type="submit" name="insert_concert" id="insert_concert" value="- Save concert-" />
  </p>
  <p>
    <label></label>
  </p>

 <?php
  if (isset($_POST['insert_concert'])){
    $enter_koncert = $_POST['enter_concert'];
    $enter_karte = $_POST['enter_tickets'];
    $enter_mesto = $_POST['enter_place'];
    $enter_datum = $_POST['enter_date'];
    $grupa_select = $_POST['band_select'];
    if (empty($enter_concert)){
  die ("ERROR: Concert name not inserted!");
  }
  $con = mysql_connect($hostname,$db_username,$db_password);
    if (!$con)
    {
        die('Could not connect: ' . mysql_error());
    }
    mysql_select_db($db_name, $con);
        $sql = mysql_query("select id from bands where bandname ='".$band_select."'");
        $band = mysql_fetch_array($sql);
    $sql = "insert into concerts values (NULL,'".$enter_concert."','".$enter_tickets."','".$enter_place."','".$enter_date."',".$band['id'].")";
    if (!mysql_query($sql,$con))
    {
        die('Error: ' . mysql_error());
    }
    echo "Data inserted into database!";
  }
  ?>

  </form>


  //Code that I need help with

   <p align="center"><font color="#0000FF">Band review</font></p>



<p align="center">Band selection: 
  <form action="show_stat.php" method="post">
   <select name="band_select" id="band_select">
      <?php
    while($row = mysql_fetch_array($sql))
    {
    echo "<option>".$row['bandname']."</option>";
    }
    ?>
    </select>
 </p>

  <p align="center">
    <input type="submit" name="band_review" id="band_review" value="- Select band-" />
  </p>

All that works, now I need mysql command that will display all concerts and other information from table concerts based on what band is selected ['bandname']

Thanks in advance

Edited by filipgothic
Member Avatar for filipgothic

thanks for info

can you give me example, because I find it very hard, its complicated because when user choose band its selected from table called bands, and I need info from table called concerts for that specific band to be displayed upon choosing, and they are connected trough id, bands table have ID same as concerts band_id

Edited by filipgothic
Member Avatar for jstfsklh211

select table1.field1 as alias1, table2.field2 as alias2
from table1
inner join table2 on table1.field = table2.matching field

Member Avatar for filipgothic

in table bands I have only ID and bandname, in table concerts I have ID,concertname,date,tickets,place and band_id (and this is same as ID from bands) I have not same name for both ID's but its same number, so is that problem?

Member Avatar for filipgothic

I need sql where id from bands for bandname (previous selected) = band_id from concerts, and display for that record all from concert

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.