PHP - Count Number Of Names Grouped

I need help with a count query and displaying it.  Right now have the following query

$query = "SELECT COUNT(*) as num1 FROM spoiler WHERE id='$id";
$total_trophies = mysql_fetch_array(mysql_query($query));
$total_trophies = $total_trophies[num1];

So I have another query that pulls how many trophies there are.  That way I can do the following


?  echo $total_trophies; echo 'of'; echo $row[trophy_count];  ?>

But the problem is sometimes in my database I could have mulitple entrys per trophy.  For Trophy 1 I might have only one record so It counts it correctly for trophy 2 I could have 3 records so It counts each one of those records when I only one to count it once for that trophy

How do you count the number of folders in a directory?

Hi all,

I am trying to use the Mysql COUNT function to count the number of records in a database where the manufacturer is the same and then generate a list of all manufacturers along with a number of associated records next to their name.

I have got the script below to show the manufacturer in a fetch array but I can not get the number next to the manufacturer to work.

A push in the right direction would be appreciated thanks.


<?php 
include("cxn.php");

$query = "SELECT manufacturer FROM sales"; 
$result = mysqli_query($cxn,$query) or die(mysqli_error());
 
?>
<select name="manufacturer" id="manufacturer">
<option value="" selected="selected">Select a Manufacturer..</option>";
<?php 
while($row = mysqli_fetch_array($result)){
$query1 = "SELECT manufacturer COUNT(*) FROM sales WHERE manufacturer='$row[manufacturer]'";  // TRYING TO COUNT THE ROWS HERE
$result1 = mysqli_query($cxn,$query1) or die (mysqli_error());

while($row1 = mysqli_fetch_array($result1)){
echo "<option value=\"202\">".$row['manufacturer'] ."[". $row1['COUNT(manufacturer)']."]</option>"; // SHOW NUMBER HERE
}
}
?>

I have a html table displaying data from mysql database. I can count rows properly with mysql_num_rows but is there a way to echo which row number it is. What I want to do is count the rows ordered by cities and echo row number. Thanks for any help.

Hello,

I am trying to create article directory for learning PHP MySql and i want to write a script for recording number of views for an article and store it in Database. Can anyone please direct me to a nice simple tutorial or help me with the steps or algorithm?

Suppose the link for an article is http://www.domain.com/article.php?id=123
Do i need to put extra variables and create a tracking PHP file for it?

I have a database lets say punch_log

The data in the table looks like:
---------------------------------------------------------------------------
|punch_log_id |  user_id | punch_id |   punch_time           |
---------------------------------------------------------------------------
|   10010         |  21         |   1       | 2010-11-10 15:04:59|
|   10011         |  21         |       2       | 2010-11-10 15:50:05|
|   10010         |  21         |   1       | 2010-11-11 15:04:59|
|   10011         |  21         |       2       | 2010-11-11 15:50:05|
|   10010         |  21         |   1       | 2010-11-12 15:04:59|
|   10011         |  21         |       2       | 2010-11-12 15:50:05|
|   10010         |  21         |   1       | 2010-11-13 15:04:59|
|   10011         |  21         |       2       | 2010-11-13 15:50:05|
|   10010         |  21         |   1       | 2010-11-14 15:04:59|
|   10011         |  21         |       2       | 2010-11-14 15:50:05|
|   10010         |  21         |   1       | 2010-11-14 15:50:59|  <-- this is why i need this.
|   10011         |  21         |       2       | 2010-11-14 15:55:05|  <-- this is why i need this.
----------------------------------------------------------------------------

Im currently using :

$kust  = $_POST['AKuu'];
$kuni3 = $_POST['LKuu'];
$valitudtootaja   = $_POST['TNimi'];


mysql_select_db($database, $con);
$query2 = "SELECT *, SUM(punch_id) FROM punch_log WHERE user_id = '".$valitudtootaja."' AND punch_time   BETWEEN '$kust' AND '$kuni3' AND punch_id ='1' ";   
$result2 = mysql_query($query2) or die(mysql_error());
while($row = mysql_fetch_array($result2)){
   $X = $row['SUM(punch_id)'];

When using the current query im getting

Workers days at work = 6
it should be 5 but thats why i need some solution to group dates and then sum them.

Does anybody have any ideas?

Folks,

Having trouble here. Forgot how you use the COUNT. Not interested in the num_rows due to it being slow compared to COUNT.

I have users table like this:

id|username|sponsor_username

0|barand|requinix

1|phpsane|alison

2|mickey_mouse|requinix

Now, I want to check if a sponsor username exists or not. Such as does the entry "requinix" exists or not in one of the rows in the "sponsor_username" column.

In our example it does twice and so the variable $sponsor_username_count should hold the value "2". Else hold "0".

I get error:

Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?' at line 1 

 

My code:

	$stmt_1 = mysqli_prepare($conn,"SELECT COUNT(sponsor_username) from users = ?"); 
            mysqli_stmt_bind_param($stmt_1,'s',$sponsor_username_count); 
            mysqli_stmt_execute($stmt_1); 
            
            //Show error if 'users' tbl was not successfully queried". 
            if (!$stmt_1) 
            { 
                echo "ERROR 1: Sorry! Our system is currently experiencing a problem loading this page!"; 
                exit(); 
            } 
            else 
            { 
                mysqli_stmt_bind_result($stmt_1,$sponsor_username_count);     
                mysqli_stmt_fetch($stmt_1); 
                mysqli_stmt_close($stmt_1); 
                
                //Check if Sponsor Username in Url ($_GET) is already registered or not. If not then show "Invalid Url" or Link message. 
                if ($sponsor_username_count < 1) 
                { 
                    echo "<b>Invalid Url or Link!<br> Invalid Sponsor: \"$sponsor_username\"!</b><?php "; 
                    exit(); 
                } 
            } 
	

Hi guys

I'm kinda stuck..so hopefully you guys can lend me a hand

I've got an array containing date elements ("Y-m-d")...

I'm trying to output some data into googlecharts..so i need to count how many elements that are in the array with todays date -1 day, todays date-2 days...todays date -3days etc... Up until a set number of days (for example 7 days, 14 days etc)..

Any advice on how to go about to achieve this?

i HAVE FOLLOWING VALUES IN FEED TABLE   a1a    a1b    a1c    a1d ...... 7         8        7         7 8         9        8         8 9         7        7         8 7        8        10        9 6         4        6         7 7        8         8        5 9        7         7        6   This in an array ( select a1a,a1b,a1c,a1d from feed where .....)   1) I want to know how many times each values has occured in the array ( eg:  7 occured 10 times, 8 occured 8 times)   2) How many 7's, 8's, 9's  are there in a1a,   howmany .....in a1b   Number of times each values occured OVERALL in array   as  well as also under each head   Please help

Hi everyone,

Like the title says I need to count the amount of 1's in a column but I can't figure out how.

Gr and thanx already
Ryflex

My Php Buddies,

I have mysql tbl columns these:

id: 
date_&_time: 
account_activation_code: 
account_activation_status: 
id_video_verification_status: 
id_verification_video_file_url: 
username: 
password: 
primary_domain: 
primary_website_email: 
sponsor_username: 
 

Now, I want to display their row data by excluded a few columns.

Want to exclude these columns:

date_&_time

account_activation_code

account_activation_status

id_verification_video_file_url

password

 

So, the User's (eg. your's) homepage inside his account should display labels like these where labels match the column names but the underscores are removed and each words' first chars CAPITALISED:

Id: 1
Id Video Verification Status: 1
Username: requinix
Primary_domain: requinix.majesty
Primary Website Email: requeen@requinix.majesty
Sponsor Username: phpsane
Recruits Number: 2

 

For your convenience only PART 1 works. Need help on Part 2

My attempted code:

 

	PART 1
	<?php 
	// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
	// Query to get columns from table
$query = $conn->query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'members' AND TABLE_NAME = 'users'");
	while($row = $query->fetch_assoc()){
    $result[] = $row;
}
	// Array of all column names
$columnArr = array_column($result, 'COLUMN_NAME');
	foreach ($columnArr as $value) { 
echo "<b>$value</b>: ";?><br><?php 
} 
	?>
	 
	PART 2
	<?php 
	//Display User Account Details 
echo "<h3>User: <a href=\"user.php?user=$user\">$user</a> Details</h3>";?><br> 
<?php 
$excluded_columns = array("date_&_time","account_activation_code","account_activation_status","id_verification_video_file_url","password"); 
foreach ($excluded_columns as $value2) 
{    
    echo "Excluded Column: <b>$value2</b><br>";
}
foreach ($columnArr as $value) 
{ 
    
    if($value != "$value2")
    { 
        $label = str_replace("_"," ","$value"); 
        $label = ucwords("$label"); 
        //echo "<b>$label</b>: "; echo "$_SESSION[$value]";?><br><?php 
        echo "<b>$label</b>: "; echo "${$value}";?><br><?php 
    }
} 
?>
	

PROBLEM: Columns from the excluded list still get displayed.

Edited November 19, 2018 by phpsane

I'm getting the dreaded " Invalid parameter number: number of bound variables does not match number of tokens" error and I've looked at this for days.  Here is what my table looks like:

|  id          | int(4)       | NO   | PRI | NULL    | auto_increment |
| user_id     | int(4)       | NO   |     | NULL    |                |
| recipient   | varchar(30)  | NO   |     | NULL    |                |
| subject     | varchar(25)  | YES  |     | NULL    |                |
| cc_email    | varchar(30)  | YES  |     | NULL    |                |
| reply       | varchar(20)  | YES  |     | NULL    |                |
| location    | varchar(50)  | YES  |     | NULL    |                |
| stationery  | varchar(40)  | YES  |     | NULL    |                |
| ink_color   | varchar(12)  | YES  |     | NULL    |                |
| fontchosen  | varchar(30)  | YES  |     | NULL    |                |
| message     | varchar(500) | NO   |     | NULL    |                |
| attachment  | varchar(40)  | YES  |     | NULL    |                |
| messageDate | datetime     | YES  |     | NULL    |        

Here are my params:

            $params = array(                 
               ':user_id' => $userid,                 
               ':recipient' => $this->message_vars['recipient'],                 
               ':subject' => $this->message_vars['subject'],                 
               ':cc_email' => $this->message_vars['cc_email'],                 
               ':reply' => $this->message_vars['reply'],                 
               ':location' => $this->message_vars['location'],                 
               ':stationery' => $this->message_vars['stationery'],                 
               ':ink_color' => $this->message_vars['ink_color'],                 
               ':fontchosen' => $this->message_vars['fontchosen'],                 
               ':message' => $messageInput,                 
               ':attachment' => $this->message_vars['attachment'],                 
               ':messageDate' => $date             );

Here is my sql:

$sql = "INSERT INTO messages (user_id,recipient, subject, cc_email, reply, location,stationery, ink_color, fontchosen, message,attachment) VALUES( $userid, :recipient, :subject, :cc_email, :reply, :location, :stationery, :ink_color, :fontchosen, $messageInput, :attachment, $date);";

And lastly, here is how I am calling it:
            $dbh = parent::$dbh;             
           $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);              
           if (empty($dbh))    return false;             
                  $stmt = $dbh->prepare($sql);             
                 $stmt->execute($params) or die(print_r($stmt->errorInfo(), true));              
           if (!$stmt) {    print_r($dbh->errorInfo());              }

I know my userid is valid and and the date is set above (I've echo'd these out to make sure).  Since the id is auto_increment, I do not put that in my sql (though I've tried that too), nor in my params (tried that too).  What am I missing?  I feel certain it is something small, but I have spent days checking commas, semi-colons and spelling.  Can anyone see what I'm doing wrong?

Currently we have a Microsoft Access database which we use to display a report, we are rolling over to PHP/SQL online for greater control e.t.c.

So far we have the report pulling all the information from the database however we need to group this information by Day followed by Employee, sorted by time and then totaled for each Employee for each day.

Below is the code we have so far which only prints columns how do we go about displaying it like the access screenshot. Attached is an example of the Microsoft Access report we have.

Code: [Select]
<?php
 $username='admin';
 $password='password';
 $database='planner';

 mysql_connect(localhost,$username,$password);
 @mysql_select_db($database) or die( "Unable to select database");
 $query="SELECT * FROM reservations ORDER BY start_date, machid, starttime";
 $result=mysql_query($query);

 $num=mysql_numrows($result);

 mysql_close();

 ?>

 <?php
 $i=0;
 while ($i < $num) {
 $f0=mysql_result($result,$i,"machid");
 $f1=mysql_result($result,$i,"start_date");
 $f2=mysql_result($result,$i,"end_date");
 $f3=mysql_result($result,$i,"starttime");
 $f4=mysql_result($result,$i,"endtime");
 $f5=mysql_result($result,$i,"first_name");
 $f6=mysql_result($result,$i,"last_name");
 $f7=mysql_result($result,$i,"company");
 $f8=mysql_result($result,$i,"phone_number");
 $f9=mysql_result($result,$i,"site_address");
 $f10=mysql_result($result,$i,"site_suburb");
 $f11=mysql_result($result,$i,"site_postcode");
 $f12=mysql_result($result,$i,"postal_address");
 $f13=mysql_result($result,$i,"postal_suburb");
 $f14=mysql_result($result,$i,"postal_postcode");
 $f15=mysql_result($result,$i,"job_type");
 $f16=mysql_result($result,$i,"payment_type");
 $f17=mysql_result($result,$i,"payment_status");
 $f18=mysql_result($result,$i,"quote_outcome");
 $f19=mysql_result($result,$i,"quote_amount");
 $f20=mysql_result($result,$i,"quote_employee");
 $f21=mysql_result($result,$i,"job_income");
 $f22=mysql_result($result,$i,"job_materials");
 $f23=mysql_result($result,$i,"invoice_number");
 $f24=mysql_result($result,$i,"job_notes");

/* START TIME */
$stime = $f3;

$shour = floor($stime/60);
$smin = str_pad($stime % 60,2,0,STR_PAD_LEFT);

//echo $hour.':'.$min;
//echo '<br><br>';

// For AM/PM
$shalf = 'am';
if( $shour > 12 ) {
$shalf = 'pm';
$shour -= 12;
}

$stime = $shour.':'.$smin.' '.$shalf;

/* END TIME */
$etime = $f4;

$ehour = floor($etime/60);
$emin = str_pad($etime % 60,2,0,STR_PAD_LEFT);

//echo $hour.':'.$min;
//echo '<br><br>';

// For AM/PM
$ehalf = 'am';
if( $ehour > 12 ) {
$ehalf = 'pm';
$ehour -= 12;
}

$etime = $ehour.':'.$emin.' '.$ehalf;

 ?>
 <table width="3500px" border="0" cellspacing="0" cellpadding="0">
 <tr>
 <td width="125px"><?php echo $f0; ?></td>
 <td width="80px"><?php echo date('d-m-y', "$f1"); ?></td>
 <td width="50px" style="text-align:right;"><?php echo $stime; ?></td>
 <td width="50px" style="text-align:right; padding-right:20px;"><?php echo $etime; ?></td>
 <td width="100px"><?php echo $f5; ?></td>
 <td width="100px"><?php echo $f6; ?></td>
 <td width="100px"><?php echo $f7; ?></td>
 <td width="100px"><?php echo $f8; ?></td>
 <td width="100px"><?php echo $f9; ?></td>
 <td width="100px"><?php echo $f10; ?></td>
 <td width="100px"><?php echo $f11; ?></td>
 <td width="100px"><?php echo $f12; ?></td>
 <td width="100px"><?php echo $f13; ?></td>
 <td width="100px"><?php echo $f14; ?></td>
 <td width="100px"><?php echo $f15; ?></td>
 <td width="100px"><?php echo $f16; ?></td>
 <td width="100px"><?php echo $f17; ?></td>
 <td width="100px"><?php echo $f18; ?></td>
 <td width="100px"><?php echo $f19; ?></td>
 <td width="100px"><?php echo $f20; ?></td>
 <td width="100px"><?php echo $f21; ?></td>
 <td width="100px"><?php echo $f22; ?></td>
 <td width="100px"><?php echo $f23; ?></td>
 <td width="100px"><?php echo $f24; ?></td>
 <?php
 $i++;
 }
 ?>
Once again thanks everyone at phpfreaks!

Ok I have some data that I would like to have grouped by date. There are dates that have multiple entries for the same date.  I would like to have that data listed under one date. I would like it to look like this

Date
        Location     Weather
        Location     Weather
Date
        Location      Weather

Here is the code I have so far and it lists the data by date but only the data for first date in the table.  If there is data listed for the same date but different location it will not list that data.  What am I missing.  Any help would be great.
!
<?php
// Connect to server and select database.
include ("opendb.php");
$tbl_name="dbnwaterfowl";

$sql="SELECT huntDate, huntLocation, huntWeather, waterfowlSpecies, waterfowlNumberKilled, waterfowlBandedNumber, DATE_FORMAT(huntDate,'%m %d, %Y') FROM dbnwaterfowl GROUP BY `huntDate` ORDER BY `huntDate` ASC";
$result=mysql_query($sql);
?>
<td>
  <p>&nbsp;</p>
  <table width="100%" border="2" cellpadding="1" cellspacing="0" bordercolor="#FA6103" bgcolor="#FFFFFF">
<tr>
<td colspan="7"><div align="center">
  <p><img src="images/hl-logo.jpg" /></p>
  <p align="center"><strong><a href="login.php">Login</a> | <a href="insert.php">Log Hunt</a> | <a href="logout.php">Logout</a></strong></p>
</div></td>
</tr>

<tr>
<td align="center" bordercolor="#FA6103" ><span class="style4">Hunting Date</span></td>
<td align="center" bordercolor="#FA6103" ><span class="style4">Hunting Location</span></td>
<td align="center" bordercolor="#FA6103"><span class="style4">Weather</span></td>
<td align="center" bordercolor="#FA6103"><span class="style4">Waterfowl Species</span></td>
<td align="center" bordercolor="#FA6103"><span class="style4">Number Killed</span></td>
<td align="center" bordercolor="#FA6103"><span class="style4">Number of Bands</span></td>
<td align="center">&nbsp;</td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td><? echo $rows['huntDate']; ?></td>
<td><? echo $rows['huntLocation']; ?></td>
<td><? echo $rows['huntWeather']; ?></td>
<td><? echo $rows['waterfowlSpecies']; ?></td>
<td><? echo $rows['waterfowlNumberKilled']; ?></td>
<td><? echo $rows['waterfowlBandedNumber']; ?></td>
<td align="center"><a href="update.php?id=<? echo $rows['id']; ?>">update</a></td>
</tr>
<?php
}
?>
</table>
</td>
</tr>
</table>
<?php
mysql_close();
?>

Howdy,

I'm trying to display text from a table in a database. It's a list of quotes, so I just need to pull out the quote and the author name. However, the quote and name are not fields in the same record; they are separate records. Example data:

Code: [Select]
quoteid    name        value
1            content You guys are great! Thanks for being awesome.
1            author John Jackson
2            content Gosh you're amazing! Always been so darn helpful!
2            author Peter Davis
So, I just need to rip out the data from 'content' and 'author', and then group them together based on the quoteid. This is my code thus far:

$testimonial_resource = mysql_query("SELECT name, value FROM quotes GROUP BY quoteid ORDER BY author ASC") or die(mysql_error());

while ($testimonial = mysql_fetch_assoc($testimonial_resource)) {

echo '<p>'.$testimonial['value']'.<br /><strong>'.$testimonial['value'].'</strong></p>';

}

Any help would be greatly appreciated for this novice.

Cheers.