PHP: If MYSQL Result greater than 0, add to array?

Question

I have a MYSQL table with a list of services that user's provide:

The Enum values in these columns can be 0 or 1. 0 represents a service not offered and 1 represents a service offered.

Cleaning   Tour Guide    Cooking    Parties
0          1             0          1

I am then running the following query In MYSQL to fetch my rows in the table:

 <?php $myBio = $conn->query("SELECT * FROM user_data, user_services WHERE user_data.user_id = user_services.user_id AND user_id = $p_id");
    if ($myBio->num_rows > 0) {
    $row = $myBio->fetch_assoc();?>

I want to generate a list of the services the user provides (where the service has a value greater than 0) - and separate the list with commas like so:

Tour Guide, Parties

I am trying to do this by using an array:

$os = array(if($row['cleaning'] > 0) { echo 'cleaning';}, if($row['tour'] >0) { echo 'Tour Guide'; });

I am trying to use PHP if statements to decipher if a service is 0 or 1 before adding it to my list.

I do not believe it is possible to combine php if statements within an array.

Please can someone show me how I can achieve my desired result? Thanks in advance

WARNING: Whenever possible use prepared statements to avoid injecting arbitrary data in your queries and creating SQL injection bugs. These are quite straightforward to do in mysqli and PDO where any user-supplied data is specified with a ? or :name indicator that’s later populated using bind_param or execute depending on which one you’re using. — tadman
– tadman, Commented Apr 1, 2019 at 14:41

Paul Spiegel · Accepted Answer · 2019-04-01 14:56:17Z

2

Use array_keys() with the optional search parameter:

$services = array_keys($row, 1);

Example:

$row = [
    'Cleaning'   => 0,
    'Tour Guide' => 1,
    'Cooking'    => 0,
    'Parties'    => 1,
];

$services = array_keys($row, 1);

var_export($services);

Result:

array (
  0 => 'Tour Guide',
  1 => 'Parties',
)

Demo

answered Apr 1, 2019 at 14:56

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adder · Accepted Answer · 2019-04-01 14:52:53Z

0

If your database columns have a speaking name, you can do it like this:

<?php
    $arrayServices = array();
    $arrayAllServices = array('Cleaning','Tour Guide','Cooking','Parties');
    foreach($arrayAllServices as $service) {
        if($row[$service] > 0) {
            $arrayServices[] = $service;
        }
    }

    $sServices = join(', ', $arrayServices);

    echo $sServices;

If the speaking names are different from the column names, you need a second array to look up translations.

answered Apr 1, 2019 at 14:52

Adder

5,8982 gold badges35 silver badges60 bronze badges

Comments

Ben Holness · Accepted Answer · 2019-04-01 14:49:29Z

0

Would something like this achieve what you want?

$results=array();
if ($row['cleaning']>0) $results[]='Cleaning';
if ($row['tour']>0) $results[]='Tour Guide';
// ...

Also, please heed @tadman's comment about prepared statements!

answered Apr 1, 2019 at 14:49

Ben Holness

2,7455 gold badges33 silver badges57 bronze badges

Comments

RanAB · Accepted Answer · 2019-04-01 14:55:43Z

<?php 
    $myResults = array();
    $myBio = $conn->query("SELECT * FROM user_data, user_services WHERE user_data.user_id = ? AND user_id = ?");
    $stmt->bind_param("ss",$user_services.user_id,$p_id);
    $stmt->execute();
    $result = $stmt->get_result();
    if($result->num_rows === 0) exit('No rows');
    while($row = $result->fetch_assoc()) 
    {
        $tempArray = array();
        $Cleaning = $row['Cleaning'];
        $Tour_Guide = $row['TourGuide'];
        $Cooking = $row['Cooking'];
        $Parties = $row['Parties'];
        if($Cleaning == 1)
            array_push($tempArray,$Cleaning)
        if($Cleaning == 1)
            array_push($tempArray,$Cleaning)
        if($Cooking == 1)
            array_push($tempArray,$Cooking )
        if($Parties == 1)
            array_push($tempArray,$Parties )
        array_push($myResults,$tempArray);
    }
?>

You will then get the myResult array which will be an array of arrays, you can then loop over the sub arrays to check values and construct the strings you intend to make.

Collectives™ on Stack Overflow

PHP: If MYSQL Result greater than 0, add to array?

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related