quick question

[nisroc]

 

$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]

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]

thank you very much

[Barand]

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]

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]

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

[nisroc]

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]

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]

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]

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]

you need to define the mysqli $db object first

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

[nisroc]

 

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]

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

[nisroc]

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