Pedro Martins Novaes Pedro Martins Novaes - 3 months ago 13
MySQL Question

Using a good SQL query instead of PHP code

I have a page that is taking a kind of long time to load, and I'm almost sure that this is caused by too many sql requests (AKA caused by my bad SQL skills). Is there anyway to join this 3 queries into one?

What I want to do with this query is to try to select a specific id from cardapios and, if there is anything there (if $num_rows > 0) the only thing I want to do is selecting that id. If there is nothing there, then I want to insert something and then select the id of that.

$query = "SELECT id FROM cardapios WHERE nome='$nome'";
$sql = mysqli_query($con,$query);
$num_rows = mysqli_num_rows($sql);
if ($num_rows > 0){
while ($row = mysqli_fetch_array($sql)){
$_SESSION['id_cardapio'] = $row['id'];
$num_rows = 0;
}}else{
$query = "INSERT INTO cardapios (nome, kcal, semana)
VALUES('$nome', '$kcal', '$semana')" or die(mysqli_error($con));
$sql = mysqli_query($con,$query);

$query = "SELECT id FROM cardapios WHERE nome='$nome' ";
$sql = mysqli_query($con, $query);
while ($row = mysqli_fetch_array($sql)){
$_SESSION['id_cardapio'] = $row['id'];
}
}


I am trying to put all of this into one query but getting nowhere. Is there anyway to use just one query for doing all of this?

Thanks in advance!

Answer

You can replace the last query by getting the mysqli_insert_id($con); as you already have the insert id available after the insert

$query = "SELECT id FROM cardapios WHERE nome='$nome'";
$sql = mysqli_query($con,$query);
$num_rows = mysqli_num_rows($sql);
if ($num_rows > 0){
    while ($row = mysqli_fetch_array($sql)){
        $_SESSION['id_cardapio'] = $row['id'];
        $num_rows = 0;
    }
}else{
    $query = "INSERT INTO cardapios (nome, kcal, semana)
        VALUES('$nome', '$kcal', '$semana')" or die(mysqli_error($con));
        $sql = mysqli_query($con,$query);

        if ( $sql !== false) {    // did insert work
            $_SESSION['id_cardapio'] = mysqli_insert_id($con);
        } else {
            // insert did nto work??
        }
}