PHP - Total Output Of Mysql Sum Query From Different Columns

Hello I am very new to php and programming and I need a grand  total of "$Total = odbc_result($result, "total");" but not sure of how to create it.

Hi guys, is there anyway to process this $result from a mysql query inside PHP so that the data below will be formatted to a pivot-like table? The number of rows and columns of the output 'table' will be indefinite. Thanks so much!

Data:
ID   Row   Col   Name
1   1   A   A1
2   2   A   A2
3   3   A   A3
4   1   B   B1
5   2   B   B2
6   3   B   B3
7   1   C   C1
8   2   C   C2
9   3   C   C3


Results:
A1(1)   A2(2)   A3(3)
B1(4)   B2(5)   B3(6)
C1(7)   C2(   C3(9)

How do I total unique id's with php or MySQL.  For instance in MySQL I have...

ID
1
1
3
4
2
1
1
3

How can I total all of the 1's, all of the twos, all of the threes and so forth. 
I have four 1's, one 2, two 3's and so forth...

Hi there,

This is what I need to do, blow that is the code I have knocked up to do it but it doesn't seem to work. The main problem is the query that matches the atc callsigns to the pilots destinations.. can't fathom it myself.

1. Pull all the records from my database.
2. Assign their unique user id's, callsigns and destinations.
3. Execute a query in which you: SELECT * FROM CLIENTS WHERE (The 'clienttype' = 'ATC') AND match the callsign of the ATC users to the 'planned_destairport' of the 'clienttype' = 'PILOT'.

For example if three people are going to Heathrow, then it would return 3 results.
4. Then do a mysql_num_rows of the result.
5. Lastly, execute a query that would update the 'atcarrivals' collumn in the database with the count number using the unique 'cid' as the identifier.

All in a loop of course.


// Selects all of the ATC Records.
$atc_arrivals_selectquery = mysql_query ("SELECT * FROM CLIENTS");

// Starts the loop for fetching the records of the previous query.
while($atc_arrivals_row = mysql_fetch_array($atc_arrivals_selectquery)){

// Sets the users cid, planned_destairport and callsign.
$atc_cid = $atc_arrivals_row['cid'];
$atc_destinations = $atc_arrivals_row['planned_destaiport'];
$atc_callysign = $atc_arrivals_row['callsign'];

// Searches the database and selects all records where the callsign is LIKE the destination airport.
$atc_arrivals_check = mysql_query("SELECT * FROM CLIENTS WHERE clienttype = 'ATC' AND callsign LIKE '%$atc_destinations'");

// Counts previous query results.
$atc_arrivals_count = mysql_num_rows($atc_arrivals_check);

// Updates the atcarrivals column with the count..
$atc_arrivals_updatequery = mysql_query("UPDATE CLIENTS SET atcarrivals = '$atc_arrivals_count' WHERE cid = '$atc_cid' AND clienttype = 'ATC'");

// End loop.
}


Concerned columns are these:
"cid" - Unique client id.
"atcarrivals" - Number of arrivals from count. (This is the problem.)
"planned_destairport" - Destination airport of pilot. Usually four letters long. IE. EGLL = Heathrow.

Thanks.

Hi friends,

I'm looking to try and echo the profits from a MySQL "totals" table for each month of the year... the table bascically consists of a "totals" column which contains the total amount spent on each order, a "suppliertotals" column which contains the total amount that it costs us to buy the goods for that order and an "orderdate" column which stores the order date as "d/m/y" (so 01/01/01).

To work out the total margin from the complete table, I calculate the sum of totals minus the sum of suppliertotals. This is our margin.

I'm currently doing the calculation with MySQL's sum() function, and using PHP explode to get the date, but I'm obviously doing something wrong since PHP is returning the same month several times with different amounts...

Let me show you the code I have; I presume I need to use some kind of foreach statement, but I'm not sure where or how... hoping you can help!



    if (!$con)  { die('Could not connect: ' . mysql_error()); }

    mysql_select_db("onetelec_wsshop", $con);

     $result=mysql_query("SELECT orderdate FROM totals", $con);

    while($row = mysql_fetch_array($result))

    {

    $ordmonth = explode("/", $row['orderdate']);

    $ordmonth = $ordmonth[1];



    switch ($ordmonth) {

    case "01":

     $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con);

    while($strow = mysql_fetch_array($stresult))

    {

    $margin = $strow['sum(total)'] - $strow['sum(stotal)'];

    }

    echo $margin." for January<br /><br />";

    break;



    case "02":

     $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con);

    while($strow = mysql_fetch_array($stresult))

    {

    $margin = $strow['sum(total)'] - $strow['sum(stotal)'];

    }

    echo $margin." for February<br /><br />";

    break;



    case "03":

     $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con);

    while($strow = mysql_fetch_array($stresult))

    {

    $margin = $strow['sum(total)'] - $strow['sum(stotal)'];

    }

    echo $margin." for March<br /><br />";

    break;



    case "04":

     $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con);

    while($strow = mysql_fetch_array($stresult))

    {

    $margin = $strow['sum(total)'] - $strow['sum(stotal)'];

    }

    echo $margin." for April<br /><br />";

    break;



    case "05":

     $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con);

    while($strow = mysql_fetch_array($stresult))

    {

    $margin = $strow['sum(total)'] - $strow['sum(stotal)'];

    }

    echo $margin." for May<br /><br />";

    break;



    case "06":

     $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con);

    while($strow = mysql_fetch_array($stresult))

    {

    $margin = $strow['sum(total)'] - $strow['sum(stotal)'];

    }

    echo $margin." for June<br /><br />";

    break;



    case "07":

     $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con);

    while($strow = mysql_fetch_array($stresult))

    {

    $margin = $strow['sum(total)'] - $strow['sum(stotal)'];

    }

    echo $margin." for July<br /><br />";

    break;



    case "08":

     $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con);

    while($strow = mysql_fetch_array($stresult))

    {

    $margin = $strow['sum(total)'] - $strow['sum(stotal)'];

    }

    echo $margin." for August<br /><br />";

    break;



    case "09":

     $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con);

    while($strow = mysql_fetch_array($stresult))

    {

    $margin = $strow['sum(total)'] - $strow['sum(stotal)'];

    }

    echo $margin." for September<br /><br />";

    break;



    case "10":

     $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con);

    while($strow = mysql_fetch_array($stresult))

    {

    $margin += $strow['sum(total)'] - $strow['sum(stotal)'];

    }

    echo $margin." for October<br /><br />";

    break;



    case "11":

     $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con);

    while($strow = mysql_fetch_array($stresult))

    {

    $margin += $strow['sum(total)'] - $strow['sum(stotal)'];

    }

    echo $margin." for November<br /><br />";

    break;



    case "12":

     $stresult=mysql_query("SELECT sum(total), sum(stotal) FROM totals WHERE ordate LIKE '%/$ordmonth/%'", $con);

    while($strow = mysql_fetch_array($stresult))

    {

    $margin += $strow['sum(total)'] - $strow['sum(stotal)'];

    }

    echo $margin." for December<br /><br />";

    break;

    }

}

  Basically I want to add up multiple tables and display a grand total on my page when the radio button is pressed. The radio button has values that connect to my database and the values link to and ID with a price. How can I use this code in order to work out a grand total? Many thanks. I have searched everywhere but found nothing.

Dear all,

I would like to find the total number of individual record in MySQL with php.

For example:
I have different department name stored in a column in MySQL.
I would like to find out the total number of each department.

But I do not know the names of the department

How can I do it?? Counting the total of each department without knowing their names

Thanks!

Below is my query which works fine:

 $query = "
          SELECT st.CourseId, c.CourseName, st.Year, st.StudentUsername, st.StudentForename, st.StudentSurname,
          s.ModuleId, m.ModuleName, m.Credits, s.SessionId, s.SessionWeight, gr.Mark, gr.Grade
          FROM Course c
          INNER JOIN Student st ON c.CourseId = st.CourseId
          JOIN Grade_Report gr ON st.StudentId = gr.StudentId
          JOIN Session s ON gr.SessionId = s.SessionId
          JOIN Module m ON s.ModuleId = m.ModuleId
          WHERE
          (st.StudentUsername = '".mysql_real_escape_string($studentid)."')
          ";

Below is my results outputted by using php:

    Course: INFO101 - Bsc Information Communication Technology Year: 3
    Student: Mayur Patel (u0867587)
   
    Module: CHI2550 - Modern Database Applications
    Session: AAB 72 (A)
   
    Course: INFO101 - Bsc Information Communication Technology Year: 3
    Student: Mayur Patel (u0867587)
   
    Module: CHI2513 - Systems Strategy
    Session: AAD 61 (B)
   
    Course: INFO101 - Bsc Information Communication Technology Year: 3
    Student: Mayur Patel (u0867587)
   
    Module: CHI2550 - Modern Database Applications
    Session: AAE 67 (B)

How do I display it using php so that it only shows Course details and Student details only once, it will show each module in the course only once and shows each session being below each module it belongs to:

The output from above should look like this in other words:

     Course: INFO101 - Bsc Information Communication Technology Year: 3
        Student: Mayur Patel (u0867587)
       
        Module: CHI2550 - Modern Database Applications
        Session: AAB 72 (A)
        Session: AAE 67 (B)
       
        Module: CHI2513 - Systems Strategy
        Session: AAD 61 (B)


PHP code to output the results:

    $output1 = "";
   
            while ($row = mysql_fetch_array($result)) { //$result is the query
              
        $output1 .= "
             <p><strong>Course:</strong> {$row['CourseId']} - {$row['CourseName']} <strong>Year:</strong> {$row['Year']}<br/>
              <strong>Student:</strong> {$row['StudentForename']} {$row['StudentSurname']} ({$row['StudentUsername']})
              </p>";
             
        $output1 .= "     
        <p><strong>Module:</strong> {$row['ModuleId']} - {$row['ModuleName']} <br/>
              <strong>Session:</strong> {$row['SessionId']} {$row['Mark']} ({$row['Grade']})
              </p>";
                }
               
                echo $output1;

Thank You

Hi Guys,
I have a query and a while loop output that I am sending to a webpage in a table format but I need some help in how best to do this so that the table columns line up with each other. Right now the column width seems to change depending on the data in the row. The code is quite simple:

<?php
include("lib/config.php");
$query = "SELECT * FROM staff_member";
$result = mysql_query($query);
?>
<html>
<style type="text/css">
<!--
  @import url(style/style.css);
-->
</style>
<head>
</head>
<body>
<h2 align="center"> Full Staff list with Dependants</font></h2> 
<?php
while($row = mysql_fetch_object($result)) {
echo "<table id=sample>
    <th>Title</th>
    <th>First Name</th>
    <th>Initial</th>
    <th>Last</th>
    <th>Agency</th>
    <th>Street</th>
    <th>Address</th>
    <th>Parish</th>
    <th>Country</th>
    <th>Office Tel#</th>
    <th>Home Tel#</th>
    <th>Office Mobile#</th>
    <th>Personal Mobile#</th>
    <th>Zone</th>
    <th>Status</th>
    <th>Location</th>
    <th>Updated by</th>

  <tr>
    <td>" .$row->title ."</td>
    <td>" .$row->first ."</td>
    <td>" .$row->initial ."</td>
    <td>" .$row->last ."</td>
    <td>" .$row->agency ."</td>
    <td>" .$row->street ."</td>
    <td>" .$row->adress ."</td>
    <td>" .$row->parish ."</td>
    <td>" .$row->country ."</td>
    <td>" .$row->officetel ."</td>
    <td>" .$row->hometel ."</td>
    <td>" .$row->officemobile ."</td>
    <td>" .$row->personalmobile ."</td>
    <td>" .$row->zone ."</td>
    <td>" .$row->status ."</td>
    <td>" .$row->location ."</td>
    <td>" .$row->updater ."</td>
   </tr>";
echo "</table> </br>";
}//close $row while
?>
</body>
</html>

Any help appreciated!

Hey guys,

I have a problem that I find rather strange, but maybe that's just me. I want to output a list of news that is stored in a MySQL database. I have coded paging ($start in the example below), but below I have narrowed my code down and removed code that is not related to the problem. The problem is that my script does not output the first row. That is, if I limit the query below from 0 to the next 10 (LIMIT 0, 10), the result will start at row 2. The same happens if $start is 10; news 12 will be the first one rather than 11. I have tried to put the numbers directly into the SQL query instead of $start, but with the same result. I have tested the query in phpMyAdmin, and the query works fine there. But for some reason, why PHP script does not output the first result. I guess I have made some silly mistake, but at the moment, I do not see it. 


<?php

// Connect to MySQL & select db

$start = 0;

$result = mysql_query("SELECT * FROM Content ORDER BY Time ASC LIMIT $start, 10");

if ($result) {
if (mysql_num_rows($result) > 0) {
$row = mysql_fetch_assoc($result);

while ($row = mysql_fetch_array($result)) {
echo '<div class="someClass">' . $row['title'] . '</div>';
}
}
}

?>

Thanks in advance!

Hello, I seem to have some problem with my script that has a goal of outputting data about the file size when a filename is queried.   The sql table name is file The table columns are as followed:  id | name | mime | size   The file name is stored in name. The script that i have that gets the file name is:

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">

	<head>
		<meta http-equiv="content-type" content="text/html;charset=utf-8" />
		<meta name="generator" content="Adobe GoLive" />
		<title>File Select</title>
					
	<!--The following script tag downloads a font from the Adobe Edge Web Fonts server for use within the web page. We recommend that you do not modify it.--><script>var __adobewebfontsappname__="dreamweaver"</script><script src="http://use.edgefonts.net/aguafina-script:n4:default.js" type="text/javascript"></script>
</head>

	 <body>
			
		<div id="title">
			  <h3 align="center">File Upload</h3>
			</div>
  <form action="result.php" method="post" name="fileID" target="_self" class="inp" AUTOCOMPLETE="ON">       
 <h1>
 
<!--Input file name-->

	<label for="fileID">File Name: </label>
              <input type="text" name='file1' id='sampleID' list="samp"> </input><br>
             <datalist id="samp">
 
<?php
$connect = mysql_connect('localhost', 'root', '');


mysql_select_db("test_db");
$query = mysql_query("SELECT * FROM  `file` ORDER BY  `file`.`name` ASC LIMIT 0 , 30");
WHILE ($rows = mysql_fetch_array($query)):
   $File_name = $rows['name'];  
  
echo "<option value=$File_name>$File_name/option> <br>";
endwhile;
?>
</datalist>

<input type="submit" class="button" >
</form>
</body>
</html>

 

Here is my code:
// Start MySQL Query for Records
$query = "SELECT codes_update_no_join_1b" .
"SET orig_code_1 = new_code_1,
       orig_code_2 = new_code_2" .
"WHERE concat(orig_code_1, orig_code_2) = concat(old_code_1, old_code_2)";
$results = mysql_query($query) or die(mysql_error());
// End MySQL Query for Records


This query runs perfectly fine when run direct as SQL in phpMyAdmin, but throws this error when running in my script??? Why is this???

Code: [Select]
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 '= new_code_1, orig_code_2 = new_code_2WHERE concat(orig_code_1, orig_c' at line 1

Ok, I know this is a really simple one, but I haven't done any coding for a couple of month and i'm a bit rusty.

I'm trying to add a field from a MySQL query to a Google calendar add button link so I can create a dynamic 'add to calendar' button from my gig list.

So on the gig list page that echo's all the dates from the database, I want to add the venue name to the 'text' section of the google URL -

Code: [Select]
<a href=\"http://www.google.com/calendar/event?action=TEMPLATE&text=VENUE HERE&dates=20060101T000000Z/20060101T000000Z&details=gig%20text&location=gig%20town&trp=false&sprop=www.the-guards.co.uk&sprop=name:The%20Guards\" target=\"_blank\"><img src=\"http://www.google.com/calendar/images/ext/gc_button6.gif\" border=0></a>
if I echo the field like so -

Code: [Select]
echo"{$row['gl_venue']}";
I get the correct result - I just can remember how to embed the php within the html, everything i've tried has failed so far.

Once i've got this sorted i can go ahead and add the date, location fields etc.

If you also have any feedback on my code, please do tell me. I wish to improve my coding base.

Basically when you fill out the register form, it will check for data, then execute the insert query. But for some reason, the query will NOT insert into the database. In the following code below, I left out the field ID. Doesn't work with it anyways, and I'm not sure it makes a difference.

Code:

  Code: [Select]
mysql_query("INSERT INTO servers (username, password, name, type, description, ip, votes, beta) VALUES ($username, $password, $name, $server_type, $description, $ip, 0, 1)");
Full code:

Code: [Select]
<?php
include_once("includes/config.php");
 
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title><? $title; ?></title>
<meta http-equiv="Content-Language" content="English" />
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<link rel="stylesheet" type="text/css" href="style.css" media="screen" />
</head>
<body>
 
<div id="wrap">
 
<div id="header">
<h1><? $title; ?></h1>
<h2><? $description; ?></h2>
</div>
 
<? include_once("includes/navigation.php"); ?>
 
<div id="content">
<div id="right">
<h2>Create</h2>
<div id="artlicles">
<?php
 
if(!$_SESSION['user'])
{
 
        $username = mysql_real_escape_string($_POST['username']);
        $password = mysql_real_escape_string($_POST['password']);
        $name = mysql_real_escape_string($_POST['name']);
        $server_type = mysql_real_escape_string($_POST['type']);
        $description = mysql_real_escape_string($_POST['description']);
 
        if(!$username || !$password || !$server_type || !$description || !$name)
        {

        echo "Note: Descriptions allow HTML. Any abuse of this will result in an IP and account ban. No warnings!<br/>All forms are required to be filled out.<br><form action='create.php' method='POST'><table><tr><td>Username</td><td><input type='text' name='username'></td></tr><tr><td>Password</td><td><input type='password' name='password'></td></tr>";
        echo "<tr><td>Sever Name</td><td><input type='text' name='name' maxlength='35'></td></tr><tr><td>Type of Server</td><td><select name='type'>
       
        <option value='Any'>Any</option>
        <option value='PvP'>PvP</option>
        <option value='Creative'>Creative</option>
        <option value='Survival'>Survival</option>
        <option value='Roleplay'>RolePlay</option>
       
        </select></td></tr>
       
        <tr><td>Description</td><td><textarea maxlength='1500' rows='18' cols='40' name='description'></textarea></td></tr>";
        echo "<tr><td>Submit</td><td><input type='submit'></td></tr></table></form>";
        }
        elseif(strlen($password) < 8)
        {
        echo "Password needs to be higher than 8 characters!";
        }
        elseif(strlen($username) > 13)
        {
                echo "Username can't be greater than 13 characters!";
        }
        else
        {
                $check1 = mysql_query("SELECT username,name FROM servers WHERE username = '$username' OR name = '$name' LIMIT 1");
               
                if(mysql_num_rows($check1) < 0)
                {
                        echo "Sorry, there is already an account with this username and/or server name!";
                }
                else
                {
                        $ip = $_SERVER['REMOTE_ADDR'];

                        mysql_query("INSERT INTO servers (username, password, name, type, description, ip, votes, beta) VALUES ($username, $password, $name, $server_type, $description, $ip, 0, 1)");
                        echo "Server has been succesfully created!";
                }
        }
       
}
else
{
        echo "You are currently logged in!";
}
 
?>
</div>
</div>
 
<div style="clear: both;"> </div>
</div>
 
<div id="footer">
<a href="http://www.templatesold.com/" target="_blank">Website Templates</a> by <a href="http://www.free-css-templates.com/" target="_blank">Free CSS Templates</a> - Site Copyright MCTop
</div>
</div>
 
</body>
</html>

I have looked all over the net on how to fix this... I am creating a baseball statistical website and I have 2 tables with identical columns (2012hitting and 2013hitting). I am trying to create a page where I can 1) group each year's statistics (which I have been able to do) and 2) have a row named "Career Totals". Each table has about 15 columns, various stats. How can I add the data from my 2012hitting table to my 2013hitting table into a Career Totals row for each player?

This is what I'm trying now:

Code: [Select]
/* CONNECTION VARIABLES */

$id = $_GET['id']; // get var from URL


/* Get data. */
$sql = "SELECT * (sum(2012hitting.hr) +sum(2013hitting.hr)) as totalhr FROM 2012hitting, 2013hitting WHERE id='$id'";
$result = mysql_query($sql);

?>


<?php
$alternate = "2";
while ($row = mysql_fetch_array($result)) {
$field1 = $row["season"];
$field2 = $row["team"];
$field3 = $row["games"];
$field4 = $row["ave"];
$field5 = $row["slg"];
$field6 = $row["r"];
$field7 = $row["h"];
$field8 = $row["rbi"];
$field9 = $row["bb"];
$field10 = $row["k"];
$field11 = $row["hr"];
$field12 = $row["dbl"];
$field13 = $row["tpl"];
$field14 = $row["sb"];
$field15 = $row["obp"];
$field16 = $row["ops"];
if ($alternate == "1") {
$color = "#ffffff";
$alternate = "2";
}
else {
$color = "#E4E4E4";
$alternate = "1";
}
echo "<tr bgcolor=$color><td align='center'>$field1</td><td align='center'>$field2</td><td align='center'>$field3</td><td align='center'>$field4</td><td align='center'>$field5</td><td align='center'>$field6</td><td align='center'>$field7</td><td align='center'>$field8</td><td align='center'>$field9</td><td align='center'>$field10</td><td align='center'>$field11</td><td align='center'>$field12</td><td align='center'>$field13</td><td align='center'>$field14</td><td align='center'>$field15</td><td align='center'>$field16</td></tr>";
}
echo "</table>";
?>
Thank you in advance!