0

So I have a table named reserveringen. inside the table there are (Reserveringsnummer(auto_increment and Unique), Voorstellingsnummer, Stoelnummer).

I also have a table named voorstelling in that table there is (Voorstellingsnummer , Tijd)

I have a select form looking like this:

<select name='Tijd'> ";
    while ($row = mysql_fetch_array($_SESSION["Tijd"])) {
        echo "<option value='" . $row['Tijd'] ."'>" . $row['Tijd'] ."</option>";
    }
    echo "</select>
    input type='submit' name='Reserveer' value='Reserveren'>
    </form>";

It will fetch an array of datetime records (named Tijd) from the table Voorstelling. the user then selects on of these times which creates problem one.

How can I succesfully get that time back as a normal string so I can save that time inside a variable. Currently when I save it inside a variable the variable will equal the value of the Submit button so in my forms case the variable that should contain the time will equal Reserveren. So how do I make it that it will save the actual time.

second question is that I have Stoelnummer in the table reserveren. one Voorstellingsnummer in reserveren (5 for example) can Have multiple different Stoelnummer so the table reserveren could look like this:

Reseveringsnummer|Voorstellingsnummer|Stoelnummer
1                | 5                 | 10
2                | 5                 | 11
3                | 4                 | 10

However Voorstellingsnummer=5 in reserveren Can't have the same Stoelnummer as another record in the table where Voorstellingsnummer=5 so the following shouldn't be possible:

Reseveringsnummer|Voorstellingsnummer|Stoelnummer
1                | 5                 | 6
2                | 5                 | 6
3                | 4                 | 10

My server needs to auto assign the Stoelnummer. so what I want for it to do is to select a random number for Stoelnummer between 1 and 50 with Voorstellingsnummer 5. Then check if that Stoelnummer is already taken for Voorstellingsnummer 5 and if not it will insert it to the server. However if the Stoelnummer is already taken it should pick another number and try again.

if all numbers are picked already the server already has a system in place that makes sure those Voorstellingnummer can't be used anymore so there will never be an infinity loop. My question is: how do I implement such a thing inside of my query which looks like this:

INSERT INTO reserveringen (`Voorstellingsnummer`, `Stoelnummer`) VALUES ('$Voorstellingsnummer', '$Stoelnummer')

currently I need to manually set $Stoelnummer to a number but that is the part that should go automatically.

I have come up with 1 solution so far but I'm not sure how to implement it. basically it goes like this:

run the query in a while loop. after inserting into the database it needs to check if there is a duplication and if not the while loop ends. However if there is a duplicate Stoelnummer for for example Voorstellingsnummer=5 then The query must be undone (so basically the Reserveringsnummer that is highest of the 2 duplicates needs to be removed) and then the entire query runs again.

Is that feasable? and if so are there any suggestions? I'm not sure if this post was too long but I really wanted to make sure my questions are clear

  • In one table but not another: Use LEFT JOIN ... IS NULL. For missing number in a consecutive list: Create a table with all possible numbers, then do the LEFT JOIN technique. – Rick James Apr 17 at 17:15
0

I actually got quite good fix for my problem using the following code:

        $randnum = rand(1, 50);
    $query = mysql_query("SELECT Stoelnummer FROM reserveringen WHERE Voorstellingsnummer = '$Voorstellingsnummer' AND Stoelnummer = '$randnum'", $db);

    while(mysql_num_rows($query) == 1){
        $randnum = rand(1, 50);
        $query = mysql_query("SELECT Stoelnummer FROM reserveringen WHERE Voorstellingsnummer = '$Voorstellingsnummer' AND Stoelnummer = '$randnum'", $db);
    }

    $stoelvrij = $randnum;

    $resultaat = mysql_query ("INSERT INTO reserveringen (`Gebruikersid`, `Voorstellingsnummer`, `Stoelnummer`) VALUES ('$username', '$Voorstellingsnummer', '$stoelvrij')", $db);

first I put a random number between 1 and 50 inside a variable and run my query. after that I check inside a while loop if the query has any rows in it. If not the while loop will not run and the random number is put inside the correct variable. If however the code does find a row it will continue to run the query until it doesn't and my database has been built in a way that there always should be 1 number that doesn't have a record already. pretty neat if I say myself (I'm a complete noob with php so this is quite the accomplishment for me)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.