Home > OS >  combine three query and get specific error alert
combine three query and get specific error alert

Time:05-11

how can i brief this queries? i want to combine them but get specific error alert and different three variable

<?php
    $sql = "SELECT content FROM post where title='tv'";
    $sql2 = "SELECT content FROM post where title='radio'";
    $sql3 = "SELECT content FROM post where title='net'";
    $result = $connection->query($sql);
    $result2 = $connection->query($sql2);
    $result3 = $connection->query($sql3);
    if ($result->num_rows > 0) {
        while($row = $result->fetch_assoc()) { 
            $tvPrice = $row['content']; 
        }
    }else{
        echo "tv error";
    }
    if ($result2->num_rows > 0) {
        while($row = $result2->fetch_assoc()) { 
            $radioPrice = $row['content']; 
        }
    }else{
        echo "radio error";
    }
    if ($result3->num_rows > 0) {
        while($row = $result3->fetch_assoc()) { 
            $netPrice = $row['content']; 
        }
    }else{
        echo "net error";
    }
?>

CodePudding user response:

Using the IN() you can return only those rows with a title of 'tv', 'radio' and 'net'. Then add the title to the query selection so you know which results a re which.

Then just amend you code to fetch all the results into a rows array and then check for the entries and report errors accordingly

<?php
// make a connection to the database obviously :)
$sql = "SELECT title, content 
        FROM post 
        WHERE title IN('tv', 'radio', 'net')";
$result = $connection->query($sql);
$rows = $result->fetch_all();

// better initialise the variables in case you try using them later
$tvPrice = $radioPrice = $netPrice = 0;

foreach ($rows as $row){
    if ($row['title'] == 'tv')) {
        $tvPrice = $row['content']; 
    }
    
    if ($row['title'] == 'radio')) {
        $radioPrice = $row['content']; 
    }

    if ($row['title'] == 'net')) {
        $netPrice = $row['content']; 
    }
}
if ( $tvPrice == 0 ) echo 'tv error';
if ( $radioPrice == 0 ) echo 'radio error';
if ( $netPrice == 0 ) echo 'net error';
?>

CodePudding user response:

Usually you don't actually need tree (or more) variables; use an array.

<?php

$titles = ['tv', 'radio', 'net'];

// Generate the query
$sql = 
    "SELECT content, title FROM post WHERE title IN(" 
        . implode(", ", array_map(function( $title ) use( $connection ) {
            return '"' . mysqli_real_escape_string($connection, $title) . '"';
        }, $titles) ) 
    . ")";

$result = $connection->query($sql);

$prices = [];

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
            
        // Check for duplicates
        if( !empty( $prices[ $row['title'] . 'Price' ] ) )
            echo $row['title'] . " has duplicate error";
        else
            $prices[ $row['title'] . 'Price' ] = $row['content'];
    }
}

// Check if we have all desires rows 
foreach( $titles as $title )
    if( empty( $prices[ $title . 'Price' ] ) )
        echo "$title missing error";

// If you really need tree variables instead of array:
extract($prices);

  • Related