Struggling to select a value from another table

Question

Im currently constructing a database using PHP, HTML and MySQL.

I have an events table. Event_id is the primary key. When someone registers for an event they input their student_id and they also select an event from a dropbox that pulls the events from my events table. I have an event_registration table which records all entries into this form. I have event_id in my event_registration table and I'm trying to make it so that when someone selects a certain event, that event's id automatically goes into the event_registration table. My PHP code at present is this:

$student_id = $_POST['student_id'];
$title = $_POST['title'];

$sql = "select event_id from events where title LIKE $title";
$db->select_db($database);
$event_id = $db->query($sql);



$q  = "INSERT INTO event_registration (";
$q .= "student_id, event_id, title";
$q .= ") VALUES (";
$q .= "'$student_id', '$event_id', '$title')";

$result = $db->query($q);

The student_id, title, and registraion_id (auto- increment) are inserting fine, but event_id is always showing up as 0, I'm not sure where I'm going wrong. Any help is appreciated.

  <td style="width: 176px; height: 23px">Event Title</td>
  <td style="height: 23px"><select name="title" style="width: 124px">
  <?php 
  include ("detail.php"); 
  $sql = mysqli_query($db, "SELECT title FROM events");
  while ($row = $sql->fetch_assoc()){
  ?>
  <option><?php echo  ($row['title']); ?></option>";
  
  <?php
  }
  ?>
  </select></td>

my dropdown code

You should really consider [using prepared statements](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). As it is, you code is vulnerable to SQL injection. Also, you do not seem to check for errors, otherwise an expression like `title LIKE $title` (without single quotes around `$title`) would raise an error if `$title` is a string. — GMB, Nov 23 '19 at 14:30
I'm still very much a beginner to web design and I'm just trying to get it running before adding error checking, thanks though — dmccaul, Nov 23 '19 at 14:36
A recommendation, I'm guessing you have the title as the value on the select menu which is not a good idea, instead put the event_id directly as values on the select and show the title as the text on the dropdown, that way you won't have to do that extra query and also use the **LIKE** option. If you can, add the code where you're constructing the dropdown menu. Now, why is this code failing? I'd check to see if i'm actually getting a number on $event_id, do a var_dump($event_id) after the first query to see what you're getting. — Juan V, Nov 23 '19 at 14:39
This is my code for the dropdown fetch_assoc()){ ?> "; i'm not sure how well it will format in a comment, how can i change this to be event id but show the title? thanks alot — dmccaul, Nov 23 '19 at 14:44
To add to what GMB said, **LIKE** is usally added when trying to match things that are like others but not exactly with for example `LIKE '$prefix%'`, but in your case if hte user is getting the title from your menu and it's supossed to be the exact title, you could simply do WHERE title = '$title'. I still thing you could avoid that query by improving the dropdown. — Juan V, Nov 23 '19 at 14:44
You can edit your question and add it there instead, but in the meantime I'll add an answer with a possible fix by changing the menu. — Juan V, Nov 23 '19 at 14:45
I tried where title = '$title' and i got nothing unfortunately — dmccaul, Nov 23 '19 at 14:45
Yes, the problem must be on the `$event_id = $db->query($sql)` because you're expecting a number but you're missing a step, you need to fetch the result you're looking for. — Juan V, Nov 23 '19 at 14:49
Ok your last comment makes a lot of sense i think i can get it — dmccaul, Nov 23 '19 at 14:52
Good, I added an answer trying to fix problem by fetching, but really consider my recommendation of building the dropdown directly with the id. When you have ids, you're better off using them than trying to match some text, try to keep the use of **LIKE** for things like searches. — Juan V, Nov 23 '19 at 14:55

score 0 · Accepted Answer · answered Nov 23 '19 at 14:53

0

The problem you seem to be having is:

$event_id = $db->query($sql);

Probably doesn't give you the number you're expecting right away, instead you need to fetch like this:

$result= $db->query($sql);
$result = $result->fetch_assoc();
$event_id = $result['event_id'];

However, based on your comment, you're constructing your dropdown with this:

<?php 
    include ("detail.php"); 

    $sql = mysqli_query($db, "title FROM events"); 

    while ($row = $sql->fetch_assoc()){ 

?> 

<option><?php echo ($row['title']); ?></option> 

<?php } ?>

You can do this instead:

<?php 
    include ("detail.php"); 

    $sql = mysqli_query($db, "SELECT event_id,title FROM events"); 

    while ($row = $sql->fetch_assoc()){ 

?> 

<option value="<?php echo $row['event_id']; ?>"><?php echo ($row['title']); ?></option>"; 

<?php } ?>

Notice how I'm also adding event_id on the select caluse. WIth this the users will see the title as expected, but you'll get the id directly with POST like this:

$event_id = $_POST['event_id'];

You DO have to change the name of the select dropdown to event_id instead of title like this:

<select name="event_id">

answered Nov 23 '19 at 14:53

Juan V

498
3
7

thanks so much your first solution worked perfect, in the long term would it be better to change the dropdown? – dmccaul Nov 23 '19 at 14:59
I wouldn't say long term but right now. It's good that you figured out why it was failing but save those changes and now try to change the dropdown instead. The thing is, imagine you have multiple events, eventually you could have two events with the same title, but ids are unique so you shouldn't rely on the title to select the one you want. Give it a try with the code above, it shoulnd't be too dificult. – Juan V Nov 23 '19 at 15:03
Yes ok that makes sense, ill make a backup and give it a try – dmccaul Nov 23 '19 at 15:05
Okay, I'll be around for a while. The steps would be (1) Change the name of the html select from **title** to **event_id** (2) On the query before you create the dropdown add **event_id,** to the fields you're getting (3) Add the **value** property on the options with the **event_id** (4) Remove the three lines for the query you were making with the LIKE (5) Create the variable event_id with **$_POST['event_id']** – Juan V Nov 23 '19 at 15:12

Struggling to select a value from another table

1 Answers1