Php/Mysql Select

[TheFilmGod]

I'm good at php but haven't coded anything in mysql/php for overa month now. I hope you can help me.

 

What I essentially want to do, is calculate the number of "e" / "u" / "p" in each column. I don't need helping echoing the results, I just need help in the mysql coding part. How do I make php/mysql count the number of "e" in a column. Then count the number of "u"?

 

Thanks for all your help in advance!

[Asperon]

more info please, do you mean count the number of times the letter "e" shows up or do you mean how many rows in column "e" contain data

[TheFilmGod]

Thanks for the reply.

 

Each row has a char limit of 1.  That is either "e" "u" or "p". I want to count how many rows in each column contain "e".

 

Sample

 

 

              Member1      Member 2

 

Meeting1        u                u

Meeting2        e                e

Meeting3        p                u

[pocobueno1388]

<?php

$query = "SELECT COUNT(*) FROM table WHERE Member1='e'";
$result = mysql_query($query)or die(mysql_error());
$num = mysql_num_rows($result);

echo "$num results returned";

?>

 

[TheFilmGod]

<?php

$query = "SELECT COUNT(*) FROM table WHERE Member1='e'";
$result = mysql_query($query)or die(mysql_error());
$num = mysql_num_rows($result);

echo "$num results returned";

?>

 

 

Although this solution would work perfectly, I do have a question. - The query would be very slow since mysql would have to select the whole table each time. I would count e for about 50 columns, then count u for all the columns and finally p. Isn't there another way around it that uses mysql commands Like count() or something?

[Asperon]

<?php
$eQuery = "SELECT yourColumn FROM yourTable WHERE yourField='e'";
$eResult = mysql_query($eQuery) or die("Query Failed: ".mysql_error());
$eNum = mysql_num_rows($eResult); //number of rows affected by the query ie. the number of fields in that column with "e"
?>

 

or

 

<?php
$eQuery = "SELECT COUNT(*) FROM yourTable WHERE yourField='e'";
$eResult = mysql_query($eQuery)or die("Query Failed: ".mysql_error());
$eNum = mysql_num_rows($eResult);
?>

 

if this isn't so helpful =p try http://www.pantz.org/database/mysql/mysqlcommands.shtml for mysql commands

[trq]

That will always return 1. Try...

 

<?php

 $query = "SELECT COUNT(*) AS num FROM table WHERE Member1='e'";
 if ($result = mysql_query($query)) {
   $row = mysql_fetch_assoc($result);
   echo $row['num'] . " results returned";
 }

?>

[trq]

I would count e for about 50 columns, then count u for all the columns and finally p. Isn't there another way around it that uses mysql commands Like count() or something?

 

Sounds to me like the database schema wasn't designed too well in the first place. Unfortunately, your pretty much stuff with the solution I just provided.

[TheFilmGod]

That will always return 1. Try...

 

<?php

  $query = "SELECT COUNT(*) AS num FROM table WHERE Member1='e'";
  if ($result = mysql_query($query)) {
    $row = mysql_fetch_assoc($result);
    echo $row['num'] . " results returned";
  }

?>

 

 

 

Thanks thorpe! This is exactly what I was looking for. I have one last question... Why do you have an if {}? Is it another alternative to die()... but an even better one, since the script won't terminate?

[trq]

Yeah, you pretty much got it. In reality I would use an else for debugging. eg;

 

<?php

  define("DEBUG",true); // this would be in a config file somewhere.

  $query = "SELECT COUNT(*) AS num FROM table WHERE Member1='e'";
  if ($result = mysql_query($query)) {
    $row = mysql_fetch_assoc($result);
    echo $row['num'] . " results returned";
  } else {
    if (DEBUG) {
      echo mysql_error() . "<br />$query";
    }
  }

?>

 

Something like that....

[TheFilmGod]

Nice! Thanks so much Thorpe for your explanation! And thanks for everyone else who helped out!

 

- You do have a nice debugging strategy Thorpe. IMO, die() should be taken out of php because its a horrible habbit for coders and doesn't achieve what you want. - I don't want this to get into a hot debate, but I'm sure there are a few out there that agree with my comment...  :-\