PHP/MySQL Query/Loop

Question

I have a database having the following structure:

<table border="2">
      <tbody>
        <tr>
          <td>ID</td>
          <td>numbering</td>
          <td>country</td>
          <td>operator</td>
          <td>rate</td>
        </tr>
        <tr>
          <td>1</td>
          <td>12345</td>
          <td>country1</td>
          <td>operator1</td>
          <td> </td>
        </tr>
        <tr>
          <td>2</td>
          <td>12345</td>
          <td>country1</td>
          <td>operator1</td>
          <td> </td>
        </tr>
        <tr>
          <td>3</td>
          <td>12345</td>
          <td>country1</td>
          <td>operator1</td>
          <td> </td>
        </tr>
        <tr>
          <td>4</td>
          <td>12345</td>
          <td>country1</td>
          <td>operator2</td>
          <td> </td>
        </tr>
        <tr>
          <td>5</td>
          <td>12345</td>
          <td>country1</td>
          <td>operator2</td>
          <td> </td>
        </tr>
        <tr>
          <td>6</td>
          <td>12345</td>
          <td>country1</td>
          <td>operator2</td>
          <td> </td>
        </tr>
      </tbody>
    </table>

I am first querying the operators based on the country selected by the user, and display them, however I also want to display the corresponding set of numbering for each operator. This is the code I implemented so far :

<?php
    $operator_rates= $wpdb->get_results("SELECT DISTINCT operator, rate FROM database WHERE country='$_GET[country]'");
            foreach ( $operator_rates as $operator_rate ) {
                $numbering = $wpdb->get_results("SELECT numbering FROM database");
                echo '
                <strong>'.$operator_rate->operator.'</strong><br/>'.$numbering;
            } ?>

The Result I am getting is as per the below:

Operator 1
Array Operator 2
Array Operator 3
Array

The result I am expecting is having each operator and exactly underneath it the set of numbering for each operator:

Operator 1
12345, 12345, 12345, 12345

Operator 2
12345, 12345, 12345, 12345

Operator 3
12345, 12345, 12345, 12345

Sorry guys for some reason the image didn't upload, I have made some text replicating the output — mario.borg
– mario.borg, Commented Dec 17, 2014 at 9:48
WARNING: You've created a dangerous SQL injection bug by putting $_GET data directly in your query. NEVER do this. Instead, use the WordPress prepared statements feature to properly escape all data parameters. — tadman
– tadman, Commented Dec 17, 2014 at 9:50
I think that in your second query you need to specify a where = operator. That said if the second query does result more then one row you need to loop that too — Marco Mura
– Marco Mura, Commented Dec 17, 2014 at 9:50
@MarcoMura it will definitely have more than one result as there is multiple operators having different numbering attached to them — mario.borg
– mario.borg, Commented Dec 17, 2014 at 9:51

vaso123 · Accepted Answer · 2014-12-17 10:17:05Z

1

$numbering is an array, because you are using $wpdb->get_results so you need to use join on that like this.

foreach ($operator_rates as $operator_rate) {
    $numbering = $wpdb->get_results("SELECT numbering FROM melitawordpress.prepaid_telephony_rates");
    echo '
                <strong>' . $operator_rate->operator . '</strong><br/>';
    $numbers = array();
    foreach ($numbering as $number) {
        $numbers[] = $number->numbering;
    }
    echo join(", ", $numbers) . "<br />";
}

EDIT: $wpdb->get_results returns with objects.

edited Dec 17, 2014 at 10:17

answered Dec 17, 2014 at 9:52

vaso123

12.4k4 gold badges37 silver badges68 bronze badges

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

6 Comments

mario.borg Over a year ago

This gave me the following error: Catchable fatal error: Object of class stdClass could not be converted to string in //directory on line 87

mario.borg Over a year ago

Seems to be getting there however I got this error: Warning: join() [function.join]: Invalid arguments passed in //directory on line 91

vaso123 Over a year ago

Oh my fault. I forgot to add it as an array the $numbers. I've editet my code. Added the [] after the variable name to add it as a new element.

mario.borg Over a year ago

for some reason its now breaking the code as only white page is loading. It seems the error is being caused for the array creation downwards as when I removed that part it works fine.

vaso123 Over a year ago

Inside the loop, comment out this: $numbers[] = $number->numbering; and say: var_dump($number) Please show the result.

|

Pupil · Accepted Answer · 2014-12-17 10:02:49Z

0

Your query is returning multiple rows.

And array is getting printed, so its showing Array.

You need to get formatted string by concatenating array elements with comma ,.

Use group_contact()

IN

$numbering = $wpdb->get_results("SELECT GROUP_CONCAT(numbering SEPARATOR ', ') AS numbering
FROM melitawordpress.prepaid_telephony_rates");

edited Dec 17, 2014 at 10:02

answered Dec 17, 2014 at 9:53

Pupil

24k6 gold badges49 silver badges71 bronze badges

Comments

vineet · Accepted Answer · 2014-12-17 10:02:50Z

0

Use implode function, e.g

foreach ($operator_rates as $operator_rate) {
$numbering = $wpdb->get_results("SELECT numbering FROM melitawordpress.prepaid_telephony_rates");
echo '
            <strong>' . $operator_rate->operator . '</strong><br/>';
echo implode(", ", $numbering) . "<br />";

}

answered Dec 17, 2014 at 10:02

vineet

14.3k11 gold badges61 silver badges76 bronze badges

Comments

tzafar · Accepted Answer · 2014-12-17 10:18:55Z

0

change this line

$numbering = $wpdb->get_results("SELECT numbering FROM database", ARRAY_A);

and change this line

<strong>'.$operator_rate->operator.'</strong><br/>'.$numbering;

with this

<strong>'.$operator_rate->operator.'</strong><br/>'.implode(',', $numbering['numbering']);

edited Dec 17, 2014 at 10:18

answered Dec 17, 2014 at 9:55

tzafar

6643 silver badges12 bronze badges

6 Comments

mario.borg Over a year ago

This is only picking up the first number attached to one operator and replicates the same value with every operator. I want to show the set of numbers attached to one operator and then show the second set of numbers attached to the second operator

tzafar Over a year ago

I have added the answer try the below one

mario.borg Over a year ago

implementing the second option gave me the following error: Catchable fatal error: Object of class stdClass could not be converted to string in //directory on line 87

tzafar Over a year ago

i have edit the answer again now try it, it should work fine

mario.borg Over a year ago

this is breaking the code, notting is loading up when i implemented your solution just white page

|

Collectives™ on Stack Overflow

PHP/MySQL Query/Loop

4 Answers 4

6 Comments

Comments

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related