|
PHP - Php Table, Count Same Values From Mysql Query
Hello there, i'm not sure if this goes to mysql, or this section, but since it's PHP based, i would say here.
So, here's the thing, i have full set up table that reads from mysql database and display it as table. Everything is working just fine, even "order" buttons that i made, now i would like to have below the table some counts, for example: My table name "canonkickoff" has column named "Prevoz" which contain only Yes and No answers, i would like to see below the table how much "Yes" answers are inside the column. Also i have other column called "VelicinaMajice", which contain 6 different answers ( XS, S, M, L, XL, XXL ), i would like to see (again, below the table) how much of every has been answered. Like: There a XS = 6 S = 1 M = 2 L = 3 And 32 answered with Yes. Could anyone help me out? Here's the full php table: <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <title>CanonKickOff 2012 Tabela.</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <link rel="stylesheet" href="table.css" type="text/css"> </head> <body style="margin: 0 0 0 0;"> <? include("passwd.php"); @$start = $_GET["start"]; if($start =='') $start =0; include("lib.php"); $link = mysql_connect($host,$username,$password); if (!$link) { die('Could not connect: ' . mysql_error()); } $db_selected = mysql_select_db($db, $link); if (!$db_selected) { die ("Can't use $db : " . mysql_error()); } //total number of records in the table $orderBy = array('Kompanija', 'id', 'ImePrezime', 'Email', 'Prevoz', 'VelicinaMajice', 'SlazemSe'); $order = 'id'; if (isset($_GET['orderBy']) && in_array($_GET['orderBy'], $orderBy)) { $order = $_GET['orderBy']; } $res = mysql_query("SELECT * from `$table` ORDER BY '.$order"); $res2 = mysql_query("SELECT * from `$table` "); @$rows = mysql_num_rows ($res2); $result = mysql_query("SELECT * from `$table` ORDER BY $order limit $start,40"); if (!$result) { die('Invalid query: ' . mysql_error()); } echo 'Sortiraj Po: <br>'; echo '<a href="?orderBy=id">ID:</a> '; echo '<a href="?orderBy=ImePrezime">Ime i Prezime:</a> '; echo '<a href="?orderBy=Kompanija">Kompanija:</a> '; echo '<a href="?orderBy=email">Email adresi:</a> '; echo '<a href="?orderBy=Prevoz">Prevoz:</a> '; echo '<a href="?orderBy=VelicinaMajice">Velicina majice:</a> '; echo '<a href="?orderBy=SlazemSe">Slazu se:</a> '; echo "<p align=center class = 'menu'> Ocitana tabela: $table </p>"; $cols = mysql_num_fields($result); $records = mysql_num_rows ($result); echo "<table align='center' width='1200' >"; echo "<tr bgcolor='BBCCDD' class='menu'>"; for ($i = 0; $i < $cols;$i++) { echo "<td align='center'>".mysql_field_name($result,$i)."</td>"; } echo "</tr>"; while ($row = mysql_fetch_array($result, MYSQL_NUM)) { echo "<tr bgcolor='F6F6F6' class='normal'>"; foreach ($row as $value) { echo "<td align='center'>".$value ."</td>"; } echo "</tr>"; } $end = $start + $records; echo "<tr align = 'center' bgcolor = 'BBCCDD' class='menu'><td colspan=$cols> $start do $end od ukupno: $rows </td></tr>"; echo "<tr align = 'center' class='mylink'><td colspan=$cols> "; if($start != 0) { $prev = $start - 40; echo "<a href='tabela.php?start=$prev'> Prethodna </a> "; } if($start<$rows-10) { $next = $start + 40; echo "<a href = 'tabela.php?start=$next'>Sledeca</a> "; } echo "</td></tr>"; echo "</table>"; ?> </body> </html> Similar TutorialsThis topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=330080.0 This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=312690.0 I insert data in mysql table row using multiple method : 1#2#3 . 1 is ID of country, 2 is Id of state, 3 is ID of town . now i have this table for real estate listings. for each list(home) i have country/state/town (1#2#3). in country table i have list of country - in country table i have list of state - in country table i have list of town. i need to The number of houses in country / state / town . my mean is : Code: [Select] USA [ 13 ] <!-- This Is equal of alabama+alaska+arizona --> ----Alabama [8] <!-- This Is equal of Adamsville+Addison+Akron --> -------Adamsville [2] -------Addison[5] -------Akron[1] ......(list of other City) ----Alaska [ 3 ] -------Avondale[3] ......(list of other City) ----Arizona [ 2 ] -------College[2] ......(list of other City) Lisintg Table : Code: [Select] ID -- NAME -- LOCATION -- DATEJOIN -- ACTIVE 1 -- TEST -- 1#2#3 -- 20110101 -- 1 2 -- TEST1 -- 1#2#3 -- 20110101 -- 1 3 -- TEST2 -- 1#3#5 -- 20110101 -- 1 4 -- TEST3 -- 1#7#6 -- 20110101 -- 1 Country Table : Code: [Select] id -- name 1 -- USA stats Table : Code: [Select] id -- countryid -- name 1 -- 1 -- albama 2 -- 1 -- alaska 3 -- 1 -- akron town Table : Code: [Select] id -- countryid -- statsid -- name 1 -- 1 -- 1 -- adamsville 2 -- 1 -- 1 -- addison 3 -- 1 -- 1 -- akron Thanks For Any Help. Hello Guys, I have a question I have the following query $result3 = mysql_query("SELECT * FROM table1 WHERE ad_id='$id2'") or die(mysql_error()); $row3 = mysql_fetch_array( $result3 ); // Grab all the var $features = $row3['features']; I am turning it into an array (comma separated) $feature2 = explode(",", $features); print_r($feature2); ) The result is like so Array ( [0] => 5 [1] => 9 [2] => 13 I want to query the features for just the ids (F_name) are for the features . This query will show all.. I would like to just display the f_name values from the array query. // build and execute the query $sql = "SELECT * FROM features"; $result = mysql_query($sql); // iterate through the results while ($row = mysql_fetch_array($result)) { echo $row['f_name']; echo "<br />"; } Please Advise.. Thanks, Dan I have posted one set of values into my database and it worked fine but when i input another set they wont go inside unless i changes the value of the primary index colum. I want to be able to insert a new values regardless of the primary index value. Any idears...? Moodle 2.5 *nix server Theme: Essential ---------------------- Hi Folks I have a small mind bender in how php is returning results from a mysql query. There are two issues: 1) The mysql query from phpmyadmin is correct, while the php function that handles the query from the website is not. 2) It takes a very long time to run this query with php, 30 seconds to over a minute. Phpmyadmin is rather quick (Query took 0.0239 seconds). The query is:
SELECT u.firstname AS 'Name' , u.lastname AS 'Surname', c.shortname AS 'Course', (
CASE
WHEN gi.itemname LIKE '%summative%' THEN 'SUMMATIVE'
WHEN gi.itemname LIKE '%formative 2%' THEN 'FORMATIVE 2'
ELSE 'MC'
END) AS 'Assessment', from_unixtime(gi.timemodified, '%d/%m/%y') AS 'Date',
IF (ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) > 70,'Yes' , 'No') AS Pass, ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) AS 'Mark', ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) AS 'Mark'
FROM mdl_course AS c
JOIN mdl_context AS ctx ON c.id = ctx.instanceid
JOIN mdl_role_assignments AS ra ON ra.contextid = ctx.id
JOIN mdl_user AS u ON u.id = ra.userid
JOIN mdl_grade_grades AS gg ON gg.userid = u.id
JOIN mdl_grade_items AS gi ON gi.id = gg.itemid
JOIN mdl_course_categories AS cc ON cc.id = c.category
WHERE gi.courseid = c.id
AND gi.itemname != 'Attendance'
AND u.firstname LIKE '%03%'
AND gi.itemname LIKE '%mative%'
ORDER BY `Name` , `Surname` , `Course`, `Assessment` ASC
When I run the query in phpmyadmin , it gives back;Name Surname Category Module Course Assessment Date Competent Mark G03 Itumeleng Velmah Mokwa Fundamentals Communications CO1 119472 FORMATIVE 2 07/04/14 Yes 100.00 G03 Itumeleng Velmah Mokwa Fundamentals Communications CO1 119472 SUMMATIVE 07/04/14 Yes 100.00 G03 Itumeleng Velmah Mokwa Fundamentals Communications CO2 119457 FORMATIVE 2 05/04/14 Yes 100.00 G03 Itumeleng Velmah Mokwa Fundamentals Communications CO2 119457 SUMMATIVE 05/04/14 Yes 88.00 G03 Lally Sheila Mokane Fundamentals Communications CO1 119472 FORMATIVE 2 07/04/14 NYC 59.00 G03 Lally Sheila Mokane Fundamentals Communications CO1 119472 SUMMATIVE 07/04/14 Yes 90.00 G03 Lally Sheila Mokane Fundamentals Communications CO2 119457 FORMATIVE 2 05/04/14 Yes 100.00 G03 Lally Sheila Mokane Fundamentals Communications CO2 119457 SUMMATIVE 05/04/14 Yes 98.00And it is perfect so I have no issues with that. Now in php I call;
function print_overview_table_groups($COURSE, $choosegroup, $fromdate, $todate, $numarray)
{
global $DB;
//check data
if(!$choosegroup){
die('No Records To Display.');
}
$thisgroup = $numarray[$choosegroup];
$sql = "SELECT DISTINCT u.firstname AS 'Name' , u.lastname AS 'Surname',
(CASE
WHEN cc.parent = '2' THEN 'Fundamentals'
WHEN cc.parent = '3' THEN 'Core'
WHEN cc.parent = '4' THEN 'Elective'
END) AS 'Category',
cc.name AS 'Module',
c.shortname AS 'Course',
(CASE
WHEN gi.itemname LIKE '%summative%' THEN 'SUMMATIVE'
WHEN gi.itemname LIKE '%formative 2%' THEN 'FORMATIVE 2'
ELSE 'MC'
END) AS 'Assessment', from_unixtime(gi.timemodified, '%d/%m/%y') AS 'Date',
IF (ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) > 70,'Yes' , 'NYC') AS Competent, ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) AS 'Mark'
FROM mdl_course AS c
JOIN mdl_context AS ctx ON c.id = ctx.instanceid
JOIN mdl_role_assignments AS ra ON ra.contextid = ctx.id
JOIN mdl_user AS u ON u.id = ra.userid
JOIN mdl_grade_grades AS gg ON gg.userid = u.id
JOIN mdl_grade_items AS gi ON gi.id = gg.itemid
JOIN mdl_course_categories AS cc ON cc.id = c.category
WHERE gi.courseid = c.id
AND gi.itemname != 'Attendance'
AND u.firstname LIKE '%03%'
AND gi.itemname LIKE '%mative%'
ORDER BY `Name` , `Surname` , `Course`, `Assessment` ASC";
return $DB->get_records_sql($sql);
}
This is returned to the index.php page from the function call;$lists = print_overview_table_groups($COURSE, $choosegroup, $fromdate, $todate, $numarray); print "<pre>"; print_r($lists); print "</pre>";The result is baffling...
Array
(
[G03 Itumeleng] => stdClass Object
(
[name] => G03 Itumeleng
[surname] => Mokwa
[category] => Fundamentals
[module] => Communications
[course] => CO2 119457
[assessment] => SUMMATIVE
[date] => 05/04/14
[pass] => Yes
[mark] => 88.00
)
[G03 Lally] => stdClass Object
(
[name] => G03 Lally
[surname] => Mokane
[category] => Fundamentals
[module] => Communications
[course] => CO2 119457
[assessment] => SUMMATIVE
[date] => 05/04/14
[pass] => Yes
[mark] => 98.00
)
)
I only get one record for each student.Can anyone help me solve this? Regards Leon Hello, I need some help. Say that I have a list in my MySQL database that contains elements "A", "S", "C", "D" etc... Now, I want to generate an html table where these elements should be distributed in a random and unique way while leaving some entries of the table empty, see the picture below. But, I have no clue how to do this... Any hints? Thanks in advance, Vero This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=342695.0 Hi all, I have the following MySQL insert query: Code: [Select] $insert= mysql_query ("INSERT INTO tablename (column1,`".$EXPfields."`) VALUES ('$something','".$EXPvalues."')"); where $EXPfields is an array of table-field-names and $EXPvalues is an array of table-field-values. Now I want to write an equivalent query, but using UPDATE instead of INSERT INTO, but I don't want to write out all the field names/values separately, but again want to use $EXPfields and $EXPvalues. So something like this: Code: [Select] $update = mysql_query ("UPDATE tablename SET (column1,`".$EXPfields."`) = ('$something','".$EXPvalues."') WHERE .... "); Is this possible? If so, what is the proper syntax? Thanks! Hi guys, I need your help. I am trying to insert the rows in the mysql database as I input the values in the url bar which it would be like this: Code: [Select] www.mysite.com/testupdate.php?user=tester&pass=test&user1=tester&email=me@shitmail.com&ip=myisp However i have got a error which i don't know how to fix it. Error: Column count doesn't match value count at row 1 <?php session_start(); define('DB_HOST', 'localhost'); define('DB_USER', 'mydbusername'); define('DB_PASSWORD', 'mydbpassword'); define('DB_DATABASE', 'mydbname'); $errmsg_arr = array(); $errflag = false; $link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD); if(!$link) { die('Failed to connect to server: ' . mysql_error()); } $db = mysql_select_db(DB_DATABASE); if(!$db) { die("Unable to select database"); } function clean($var){ return mysql_real_escape_string(strip_tags($var)); } $username = clean($_GET['user']); $password = clean($_GET['pass']); $adduser = clean($_GET['user1']); $email = clean($_GET['email']); $IP = clean($_GET['ip']); if($username == '') { $errmsg_arr[] = 'username is missing'; $errflag = true; } if($password == '') { $errmsg_arr[] = 'PASSWORD is missing'; $errflag = true; } if($errflag) { $_SESSION['ERRMSG_ARR'] = $errmsg_arr; echo implode('<br />',$errmsg_arr); } else { $sql = "INSERT INTO `members` (`username`,`email`,`IP`) VALUES ('$adduser','$email','$IP')"; if (!mysql_query($sql,$link)) { die('Error: ' . mysql_error()); } echo "The information have been updated."; } ?> Here's the name of the columns i have got in my database: Code: [Select] username IP I have input the correct columns names, so I can't correct the problem I am getting. Please can you help? What gets put in for the values is my problem. I have tried every way I can think of but I get errors in the code or I don't get the row inserted. Can anyone give me a code sample using $ variables, preferably a multi-line list? I would have to fix up my code to show you what I am trying as it is in between tries now. I have to be doing something wrong and may have to put together a simple test script but a working example would be better. Everything I look at on the web shows "xxxxx xxxx" examples not $variables. Thanks in advance. Hello Guys, I am trying to insert a value in my table like this image.jpg,image2.jpg,image3.jpg Currently it just runs the image names together Here is my code.. I tried to use explode, but it just adds the word "array" in the table. Please advise $filename2 = $phstring; } $csv = $filename2; $fphoto = explode(',', $csv); // Insert ad details into mysql $query = "INSERT INTO ads (ad_type,ad_title,ad_body,ad_category,ad_photo,ad_member,ad_status,ad_city,ad_state,ad_zip)VALUES('".$ad_type."','".$ad_title."','".$ad_body."','".$catid."','".$fphoto."','".$vname."','".$ad_status."','".$city."','".$state."','".$zip."')"; Apologies... I'm a noob trying to reverse engineer code. I have a script that can retrieve values from a MySQL database (based on a session login that recognizes which user you are) and places those values into the text fields of a form, pre-populating the form. Code: [Select] <?php mysql_connect('localhost', 'db', 'password'); mysql_select_db('users'); $id = mysql_real_escape_string($_SESSION['userid']); $select = "SELECT * FROM user_info WHERE md5(Member_No) = '$id';"; $query = mysql_query($select); if ($row = mysql_fetch_array($query)) { ?> <form> FIRST name: <input type="text" name="First" value="<?php echo stripslashes($row['First']); ?>" LAST name: <input type="text" name="Last" value="<?php echo stripslashes($row['Last']); ?>" > Birthday: <input type="text" name="Birthday" value="<?php echo stripslashes($row['Birthday']); ?>" <input type="submit" value="Submit"> </form> <?php } ?> I want to be able to connect to that same database and update ALL the values (3 in the example above) with the single click of the form button. I think I was told some time ago that it's not possible to update all the values at once? Thanks, ~Wayne Hi, i don't understand how to show ENUM type data in php. Now i use: <input type="text" name="type" style="width: 362px; height: 35px;" value="<?php echo $type; ?>"> But it print only the value that is set to topic i see. How to display all ENUM values in radio buttons and that checked button would be that one from the topic. For example: ENUM ('city','town','vilage') . I select town when writing a topic. And when i edit data i see 3 radio buttons and TOWN would be checked. Thank you for answer. p.s maybe someone know a good site where is a tutorial how to show enum in php Hi all, I have a table... id | Name | Downloads | 01 | tom | 5 02 | thomas | 2 03 | tommy | 15 I need a button in my page whcih on submit would resent the 'downloads' column so all values are set to 0, Is this possible and how if it is? Thanks for any advice I am pulling 5 addresses and I am wanting to store these 5 addresses in columns in a separate table. On the query string I put LIMIT 5. How can I store the results of each of the 5 loops into columns in a separate table? The columns are A1(for name), A2(for address), A3 (for city), A4 (for state), A5 (for zip), A6 (for phone), A7 (for miles), .... then I also have B1-B7, C1-C7, D1-D7, and E1-E7. I am basically wanting to store up to 5 addresses in an array and then update them into a mysql table. Code: [Select] foreach ($zips as $key => $value) { $result = mysql_query("SELECT State FROM extreme WHERE Zip = '$key' AND Type='store' AND `InStore` <> 'NO' LIMIT 5", $db); $num_rows = mysql_num_rows($result); while($myrow = mysql_fetch_array ($result)) { $Dealer=$myrow['Dealer']; $Address=$myrow['Address']; $City=$myrow['City']; $State=$myrow['State']; $Zip=$myrow['Zip']; $Phone=$myrow['Phone']; $Miles = $value; } } Hey guys, so I have this script:
<?php $databasehost = "localhost"; $databasename = "import"; $databasetable = "import"; $databaseusername="import"; $databasepassword = "password"; $fieldseparator = ","; $lineseparator = "\n"; $csvfile = "test.csv"; if(!file_exists($csvfile)) { die("File not found. Make sure you specified the correct path."); } try { $pdo = new PDO("mysql:host=$databasehost;dbname=$databasename", $databaseusername, $databasepassword, array( PDO::MYSQL_ATTR_LOCAL_INFILE => true, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION ) ); } catch (PDOException $e) { die("database connection failed: ".$e->getMessage()); } $affectedRows = $pdo->exec(" LOAD DATA LOCAL INFILE ".$pdo->quote($csvfile)." REPLACE INTO TABLE `$databasetable` FIELDS TERMINATED BY ".$pdo->quote($fieldseparator)." LINES TERMINATED BY ".$pdo->quote($lineseparator)); echo "Loaded a total of $affectedRows records from this csv file.\n"; ?> This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=328883.0 Hey guys having a bit of bother here, I don't understand why my PHP script isn't working.
So I will first show the code and then go over the issue I am having:
include('select-stock.php');
include('db-affinity/header-main.php'); ?>
<?php $carId = $_GET['id']; ?>
<?php
try {
$carview = $db->prepare("SELECT Make, Model, Colour, FuelType, Year, Mileage, Bodytype, Doors, Variant, EngineSize, Price, Transmission, PictureRefs, ServiceHistory, PreviousOwners, Options, FourWheelDrive, FullRegistration FROM import WHERE FullRegistration = $carId");
} catch (Exception $e) {
echo "Error.";
exit;
}
$cardata = $carview->fetch(PDO::FETCH_ASSOC)
?>
<div class="container">
<div class="row">
<div class="col-md-12 col-sm-12">
<?php echo "$carId"; ?>
<?php echo mysql_errno($carview) ?>
<?php echo '<ul class="overwrite-btstrp-ul other-specs-ul h4-style">
<li>Mileage: '.number_format($cardata["Mileage"]).'</li>
<li>Engine size: '.$cardata["EngineSize"].'cc</li>
</ul>'
?>
</div>
</div>
</div>
<?php include('db-affinity/footer.php') ?>
So basically what I am trying to achieve from this code is giving my page dynamic content based on if the the ?id= of a URL matches a row of my 'FullRegistration' column.
So for example if I have a URL like this "www.cars.com/carview.php?id=NG61CWJ" I then want my script check if there is a row that has that value in the 'FullRegistration' column of my table and then echo out the results of certain columns of that row like this example currently in my code:
<?php echo '<ul class="overwrite-btstrp-ul other-specs-ul h4-style"> <li>Mileage: '.number_format($cardata["Mileage"]).'</li> <li>Engine size: '.$cardata["EngineSize"].'cc</li> </ul>' ?>In theory FROM import WHERE FullRegistration = $carIdshould make this happen however for some reason on my server when I use the script above I get nil results returned instead of the results of the row that matches the GET id I get: Mileage: 0 Engine size: cc I am aware my code is insecure at the moment however it isn't an issue at this moment in time. Any ideas why I might be getting nil results returned, my other queries to this table have worked flawlessly however I am having bother with this one, can you see anything in this code that might cause this issue? Here is the database connection file that is included at the top of the code block just in case this could be a bit of a problem:
<?php
include('database.php');
try {
$results = $db->query("SELECT Make, Model, Colour, FuelType, Year, Mileage, Bodytype, Doors, Variant, EngineSize, Price, Transmission, PictureRefs, ServiceHistory, PreviousOwners, Options, FourWheelDrive, FullRegistration FROM import ORDER BY Make ASC");
} catch (Exception $e) {
echo "Error.";
exit;
}
///carousel-vehicle results
try {
$fourresults = $db->query("SELECT Make, Model, Colour, FuelType, Year, Mileage, Bodytype, Doors, Variant, EngineSize, Price, Transmission, PictureRefs, ServiceHistory, PreviousOwners, Options, FourWheelDrive FROM import ORDER BY Make LIMIT 0, 4");
} catch (Exception $e) {
echo "Error.";
exit;
}
try {
$fourresultsone = $db->query("SELECT Make, Model, Colour, FuelType, Year, Mileage, Bodytype, Doors, Variant, EngineSize, Price, Transmission, PictureRefs, ServiceHistory, PreviousOwners, Options, FourWheelDrive FROM import ORDER BY Make LIMIT 4, 4");
} catch (Exception $e) {
echo "Error.";
exit;
}
try {
$fourresultstwo = $db->query("SELECT Make, Model, Colour, FuelType, Year, Mileage, Bodytype, Doors, Variant, EngineSize, Price, Transmission, PictureRefs, ServiceHistory, PreviousOwners, Options, FourWheelDrive FROM import ORDER BY Make LIMIT 8, 4");
} catch (Exception $e) {
echo "Error.";
exit;
}
try {
$makeFilter = $db->query("SELECT DISTINCT Make FROM import ORDER BY Make ASC");
} catch (Exception $e) {
echo "Error.";
exit;
}
try {
$modelFilter = $db->query("SELECT DISTINCT Model FROM import ORDER BY Make ASC");
} catch (Exception $e) {
echo "Error.";
exit;
}
?>
All of these queries are working flawlessly on the live site so the db connection is obviously working. |
|||||||