quick question

nisroc · January 11, 2014

$query_TopNumbers = "SELECT Number1, COUNT(Number1) AS CT FROM Draws GROUP BY Number1 ORDER BY COUNT(Number1) DESC";

I am curious if it is possible to set up "Number1" in the query to be dynamic. i need to count off the columns and the count repeating values which the query is doing. I have 6 columns, Number1, Number2, Number3, Number4, Number5, Number6. Is it possible to make a query such as "SELECT Number . $colNumber, COUNT(Number . $colNumber) AS CT FROM Draws GROUP BY Number . $colNumber ORDER BY COUNT(Number . $colNumber) DESC"; and just loop it? or is there an easier way. I need the results in a table.

Psycho · January 11, 2014

Don't run queries in loops. based upon your sample query you just want a count of each column that has values. Try this

SELECT COUNT(Number1) AS CT1, COUNT(Number2) AS CT2, COUNT(Number3) AS CT3,
       COUNT(Number4) AS CT4, COUNT(Number5) AS CT5, COUNT(Number6) AS CT6
FROM Draws

nisroc · January 11, 2014

thank you very much

Barand · January 11, 2014

I believe he wants counts of the occurences of each number

SELECT colno, num, COUNT(*) AS CT  
FROM 
    (
    SELECT 1 as colno, Number1 as num FROM Draws
    UNION
    SELECT 2 as colno, Number2 as num FROM Draws
    UNION
    SELECT 3 as colno, Number3 as num FROM Draws
    UNION
    SELECT 4 as colno, Number4 as num FROM Draws
    UNION
    SELECT 5 as colno, Number5 as num FROM Draws
    UNION
    SELECT 6 as colno, Number6 as num FROM Draws
    ) as balls
GROUP BY colno, num
ORDER BY colno, CT DESC

nisroc · January 12, 2014

I spent time trying to get it to work ... both example however nothing so I will try explain myself a little better. I am trying to learn PhP/MySQL by creating a lotto script that shows the most numbers drawn currently in each column. The lotto is drawn with 7 numbers one being a bonus number (do not need for this query). My table is populated with columns ID, Date, Number1 to Number6 and Bonus. My idea is to take Number1 col and find out the most called numbers from top to bottom in it and list them in a table which i have accomplished. However I got stuck when i wanted to do the same thing for Number2 to Number 6 (including Number1) all in one query.

with what i have before i added anything else the results came out like this for column Number1

1st number #

1 371

2 330

4 295

3 290

5 226

...

38 1

i just wish to do the same thing with number1 though number6 and add them to one HTML table.

PS thank for the ideas... playing with them taught me more about things i did not understand correctly

Edited January 12, 2014 by nisroc

Barand · January 12, 2014

In my query, change all "UNION" to "UNION ALL"

nisroc · January 12, 2014

SELECT colno, num, COUNT(*) AS CT  
FROM 
    (
    SELECT 1 as colno, Number1 as num FROM Draws
    UNION ALL
    SELECT 2 as colno, Number2 as num FROM Draws
    UNION ALL
    SELECT 3 as colno, Number3 as num FROM Draws
    UNION ALL
    SELECT 4 as colno, Number4 as num FROM Draws
    UNION ALL
    SELECT 5 as colno, Number5 as num FROM Draws
    UNION ALL
    SELECT 6 as colno, Number6 as num FROM Draws
    ) as balls
GROUP BY colno, num
ORDER BY colno, CT DESC";

error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"' at line 17

nvm dumb and blind i got it

Edited January 12, 2014 by nisroc

nisroc · January 12, 2014

This now works but with a problem of the table. everything is listed in 2 columns and i would like to list it as

<table>
<tr>
<td>Number1</td><td>#</td>
<td>Number2</td><td>#</td>
<td>Number3</td><td>#</td>
<td>Number4</td><td>#</td>
<td>Number5</td><td>#</td>
<td>Number6</td><td>#</td>
</tr>
</table>

this is my current table code

<table border="0">
  <tr>
    <td>1st number</td>
    <td>#</td>
  </tr>
  <?php do { ?>
    <tr>
      <td><?php echo $row_TopNumbers['num']; ?></td>
      <td><?php echo $row_TopNumbers['CT']; ?></td>
    </tr>
    <?php } while ($row_TopNumbers = mysql_fetch_assoc($TopNumbers)); ?>
</table>

Barand · January 12, 2014

try

$sql = "SELECT colno, num, COUNT(*) AS CT  
FROM 
    (
    SELECT 1 as colno, Number1 as num FROM Draws
    UNION ALL
    SELECT 2 as colno, Number2 as num FROM Draws
    UNION ALL
    SELECT 3 as colno, Number3 as num FROM Draws
    UNION ALL
    SELECT 4 as colno, Number4 as num FROM Draws
    UNION ALL
    SELECT 5 as colno, Number5 as num FROM Draws
    UNION ALL
    SELECT 6 as colno, Number6 as num FROM Draws
    ) as balls
GROUP BY colno, num
ORDER BY num, colno";

echo "<h3>Frequency table</h3>
    <table border='1' style='border-collapse:collapse'>
    <tr><th></th><th>Number1</th><th>Number2</th><th>Number3</th><th>Number4</th><th>Number5</th><th>Number6</th></tr>\n";
$res = $db->query($sql);
$currNum = 0;
while (list($col,$num,$ct) = $res->fetch_row()) {
    if ($num != $currNum) {
        if ($currNum) {
            echo "<tr><th>$currNum</th><td>" . join('</td><td>',$freqs) . "</td></tr>\n";
        }
        $freqs = array_fill_keys(range(1,6),0);
        $currNum = $num;
    }
    $freqs[$col] = $ct;
}
echo "<tr><th>$currNum</th><td>" . join('</td><td>',$freqs) . "</td></tr>\n";
echo "</table>\n"

nisroc · January 12, 2014

No go trying to figure out a error; Fatal error: Call to a member function query() on a non-object in D:\xampp\htdocs\test.php on line 51

Before you gave me this idea i was working on a if else theory but needed to change colno to rownum. I am not sure rownum would have worked the same way with the sql

<?php require_once('Connections/Lotto.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }
 
  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
 
  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}
 
mysql_select_db($database_Lotto, $Lotto);
$sql = "SELECT colno, num, COUNT(*) AS CT   FROM      (     SELECT 1 as colno, Number1 as num FROM Draws     UNION ALL     SELECT 2 as colno, Number2 as num FROM Draws     UNION ALL     SELECT 3 as colno, Number3 as num FROM Draws     UNION ALL     SELECT 4 as colno, Number4 as num FROM Draws     UNION ALL     SELECT 5 as colno, Number5 as num FROM Draws     UNION ALL     SELECT 6 as colno, Number6 as num FROM Draws     ) as balls GROUP BY colno, num ORDER BY num, colno; ";
$Recordset1 = mysql_query($sql, $Lotto) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
 
<body>
<?php echo "<h3>Frequency table</h3>
    <table border='1' style='border-collapse:collapse'>
    <tr><th></th><th>Number1</th><th>Number2</th><th>Number3</th><th>Number4</th><th>Number5</th><th>Number6</th></tr>\n";
$res = $database_Lotto->query($sql);
$currNum = 0;
while (list($col,$num,$ct) = $res->fetch_row()) {
    if ($num != $currNum) {
        if ($currNum) {
            echo "<tr><th>$currNum</th><td>" . join('</td><td>',$freqs) . "</td></tr>\n";
        }
        $freqs = array_fill_keys(range(1,6),0);
        $currNum = $num;
    }
    $freqs[$col] = $ct;
}
echo "<tr><th>$currNum</th><td>" . join('</td><td>',$freqs) . "</td></tr>\n";
echo "</table>\n"
?>
</body>
</html>
<?php
mysql_free_result($Recordset1);
?>

Edited January 12, 2014 by nisroc

Barand · January 12, 2014

you need to define the mysqli $db object first

$db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE); // use your credentials

nisroc · January 12, 2014

you need to define the mysqli $db object first
$db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE); // use your credentials
tried and worked! Thanks but why would it not work with my include credentials? The include at very top of the file holds hosts, user, pass and db info

Barand · January 12, 2014

Because you were using mysql which is deprecated. I am using mysqli (as you should be)

nisroc · January 12, 2014

Because you were using mysql which is deprecated. I am using mysqli (as you should be)

ah I stop doing that. Thanks again for all your help

Sign In

quick question

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information