How to select single matching record if it exists, otherwise select all with default '0' value?

Question

SELECT baza WHERE owner='bako' else owner='0'

I would like to see a listing of all the jobs not yet assigned to an owner, but once I choose one of them and I refresh the page I would like to see only the one I chose.

Something similar to this:

SELECT baza WHERE owner='bako' else owner='0'

Right now I have this:

$sql = "SELECT * FROM baza WHERE owner='0' ";

but I want like this:

$username = $_SESSION['username']; 
$sql = "SELECT * FROM baza WHERE owner='$username' else owner='0'";

This is my PHP

<?php
header("charset: UTF-8");
$conn = mysqli_connect("localhost", "root", "", "dam");
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$username = $_SESSION['username'];


$sql = "SELECT * FROM `baza`  WHERE owner='0' ";


mysqli_set_charset($conn, "utf8");
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    // output data of each row


    while($row = $result->fetch_assoc()) {
        echo 

        "
         <br><br><br> <br><br>
        <div id='names_bord'> 

        <div id='names_name'>  name: " . $row["saxeli"] . " </div>
        <div id='names_price'>  price: " . $row["tanxa"] . " </div>
        <div id='names_a'>     city: " . $row["qalaqi"]. " " . $row["misamarti"]. "</div>
        <div maxlength='10' id='names_a'>   comment: " . $row["komentari"]. " </div>

        <div id='names_accept'><a href=\"detail - Copy.php?id=".$row['id']."\"><img src='infoicon.png' ></i></a></div>

         </div>

        ";

    }

} else {
    echo "0 results"; 
}

So you want the `baza` column if `owner = 'bako'`. If `owner` is not `'bako'`, then you want the `baza` column to be `0` or you want the `baza` column where `owner = '0'`? — dvo, Oct 16 '19 at 13:48
baza column where owner = '0' if owner not 'bako' in real i want, when i agree 1 job i am owner in this job and other agents can not see this job but then i refresh this site if i have 1 active job i can not see other jobs, i can see only own job right now i have this: $sql = "SELECT * FROM `baza` WHERE owner='0' "; but i want like this : $username = $_SESSION['username']; $sql = "SELECT * FROM `baza` WHERE owner='$username' else owner='0'"; thank for comment — bnoz, Oct 16 '19 at 14:06
`else` is not a valid word in SQL. Maybe you mean `OR`. Or maybe not. Your description is still rather confusing. It would really help if you could provide some sample data from your table, and the expected result of your query. — ADyson, Oct 16 '19 at 14:21
@ADyson thank you, OR can not help me, i want only one owner=username or if username dont have active job and not writing in owners lines , can see all jobs (default owner=0) for see all jobs and take one of them. — bnoz, Oct 16 '19 at 14:35
You might need two queries. First query checks if the user has any active jobs. If that returns no rows, then run the second query to get all jobs. Use PHP to control whether you need the second query or not. — ADyson, Oct 16 '19 at 14:39
just a note: you're open to SQL injection and should look to resolve — treyBake, Oct 16 '19 at 16:17
It is a very bad idea to use `die($conn->connect_error);` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Oct 16 '19 at 16:36
Your `owner` field should be possible to set to NULL. If not owner is set it should be set to NULL. Setting to string 0 is very confusing and not recommended. — Dharman, Oct 16 '19 at 16:40
@Dharman thank, you are right and can you help my with (if else) or something like it — bnoz, Oct 16 '19 at 16:51
It would also be helpful if you could build a working http://www.sqlfiddle.com/ for us to try on. — Dharman, Oct 16 '19 at 16:53
"can tell me how to do?". Yes...write two separate queries and execute them. The process for executing a second query is the same as for executing the first one. If you want to check if the first query returns any result then use https://www.php.net/manual/en/mysqli-result.num-rows.php — ADyson, Oct 16 '19 at 17:11

score 0 · Answer 1 · answered Oct 18 '19 at 07:19

So, I solved this problem, and now I wonder how correct it is, according to experienced people

<?php
header("charset: UTF-8");
$conn = mysqli_connect("localhost", "root", "", "dam");
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// firs if it is
$username = $_SESSION['username'];
$sql = "SELECT * FROM `baza` WHERE owner='$username' && kategoria='' ";

mysqli_set_charset($conn, "utf8");
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row

while($row = $result->fetch_assoc()) {
echo "
<br><br><br> <br><br>
<div id='names_bord'> 
<div id='names_name'>  სახელი: " . $row["saxeli"] . " </div>
<div id='names_price'>  თანხა: " . $row["tanxa"] . " </div>
<div id='names_a'>     ქალაქი: " . $row["qalaqi"]. " " . $row["misamarti"]. "</div>
<div maxlength='10' id='names_a'>   კომენტარი: " . $row["komentari"]. " </div>
<div id='names_accept'><a href=\"detail - Copy.php?id=".$row['id']."\"><img src='infoicon.png' ></i></a></div>
</div>";}

} else { 
// else if it is
$sqll = "SELECT * FROM `baza` WHERE owner='0' && kategoria='' ";

mysqli_set_charset($conn, "utf8");
$result = $conn->query($sqll);
if ($result->num_rows > 0) {
// output data of each row

while($row = $result->fetch_assoc()) {
echo "
<br><br><br> <br><br>
<div id='names_bord'> 
<div id='names_name'>  სახელი: " . $row["saxeli"] . " </div>
<div id='names_price'>  თანხა: " . $row["tanxa"] . " </div>
<div id='names_a'>     ქალაქი: " . $row["qalaqi"]. " " . $row["misamarti"]. "</div>
<div maxlength='10' id='names_a'>   კომენტარი: " . $row["komentari"]. " </div>
<div id='names_accept'><a href=\"detail - Copy.php?id=".$row['id']."\"><img src='infoicon.png' ></i></a></div>
</div>";}

 }
}
$conn->close();
?>

How to select single matching record if it exists, otherwise select all with default '0' value?

1 Answers1