MySQL Query in PHP

Question

This is my actual query in MySQL:

 SELECT sndprefix, COUNT(*) FROM `inbox` WHERE
 (
 sndprefix='22' OR 
 sndprefix='23' OR 
 sndprefix='32' OR 
 sndprefix='33' OR 
 sndprefix='34' OR 
 sndprefix='42' OR 
 sndprefix='43'
 ) 
 GROUP BY sndprefix;

I'm getting the CORRECT response such as:

 sndprefix  COUNT(*)
 22     3
 23     5
 32     1
 33     1
 43     1

My question is what is the PHP code to show this query in the browser and to be parsed as json format too.

Thanks in advance.

Apology if I have not posted the code earlier, I was trying to familiarize myself with stackoverlow's UI finding the edit option..

Here's the code I've tried so far:

 <?php
 $query="SELECT sndprefix, COUNT(*) FROM `inbox` WHERE
 (
 sndprefix='22' OR 
 sndprefix='23' OR 
 sndprefix='32' OR 
 sndprefix='33' OR 
 sndprefix='34' OR 
 sndprefix='42' OR 
 sndprefix='43'
 ) 
 GROUP BY sndprefix;";

 $result = mysql_query($query);
while($sunget = mysql_fetch_array($result)){
    foreach($sunget AS $key => $value) { $conget[$key] = stripslashes($value); }
 echo "". nl2br( $sunget['sndprefix']) ."";
 }
 ?>

Thanks.

not question-related, but wouldn't it be better if you used sndprefix IN ('22','23','32','33','34','42','43')? — Nadir Sampaoli
– Nadir Sampaoli, Commented Jun 10, 2012 at 12:11
@JohnSantos, you have got to be kidding. stackoverflow.com is not a "please code this for me" service... you need to show that you have tried something. Google turns up lots of results for "php mysql query" and "php json". — d11wtq
– d11wtq, Commented Jun 10, 2012 at 12:12
@Hassan Don't ever direct people to that site. This is especially the case for that idiotic mysql "tutorial". They are suggesting you make your aplpication vulnerable to SQL injection at best. — PeeHaa
– PeeHaa, Commented Jun 10, 2012 at 12:29
@Hassan First starters mysql_* will be deprecated and here is just a huge security hole without any mention about it. Hence: don't direct people to that site for the better of the web. — PeeHaa
– PeeHaa, Commented Jun 10, 2012 at 12:41
@Hassan Can we please stop pretending that people who need "A simple first introduction to a topic" don't immediately copy/paste that code onto a production site. No, I disagree: topical introductions should go out of their way to cover the major security holes. — user895378
– user895378, Commented Jun 10, 2012 at 12:49

tereško · Accepted Answer · 2012-06-10 16:05:13Z

Well .. looks like question got updated.

You are doing it wrong .. at multiple levels. I guess bad tutorials are at fault.

The query

You should read more about WHERE clause, and how to use it. This page will give some basic info, but you should find some book about MySQL (and only about MySQL, no PHP included).

This would make the query much easier to read:
```
SELECT 
    sndprefix AS prefix, 
    COUNT(*) AS count 
FROM inbox 
WHERE sndprefix IN ('22', '23', '32', '33', '34', '42', '43') 
GROUP BY sndprefix;
```
Also, you might want to learn about JOIN statements in MySQL, because I get a feeling, that this list of specific prefixes has something in common.

And since the prefixes are repeating, they might be better off in a separate table. At least from normalization point of view.

Querying database in PHP

You should not writing new code with the ancient mysql_* functions. They are no longer maintained and community has begun the deprecation process, and now there is even has been a warning added in manual: (see the red box) .

Instead you should learn about prepared statements and use either PDO or MySQLi. If you cannot decide, this article will help to choose. If you care to learn, here is a quite good PDO-related tutorial.

Lets assume that you have use PDO to interact with database (I just prefer it over MySQLi).

$connection = new PDO(
                  'mysql:host=localhost;dbname=my_magc_db;charset=UTF-8', 
                  'username', 'password');
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
// the connection has been established, and object configured

$sql = 'SELECT 
            sndprefix AS prefix, 
            COUNT(*) AS count 
        FROM inbox 
        WHERE sndprefix IN ('22', '23', '32', '33', '34', '42', '43') 
        GROUP BY sndprefix';

$statement = $connection->query( $sql );
// no need to prepare the statement this time, because values are predefined
// if values '22', '23', etc. depend on user input, code would be different

$data = $statement->fetchAll(PDO::FETCH_ASSOC);
// now $data contains all the values.
// you van use var_dump($data) to "look inside" variable

making JSON

Since $data already is an array with all the values, you can simply write this
echo json_encode( $data ); to generate JSON.

making output

Since you have already the array, you can just write:

foreach ( $data as $row ) {
    echo $row['prefix'], ' ', $row['count'], PHP_EOL, '<br>';
}

Any questions ?

Collectives™ on Stack Overflow

MySQL Query in PHP

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related