PHP - Pivot Table Using Php Codes
hello,
please can anyone help me out with the codes I want to pivot it Item ID Year Type Item Value 68 2012 Assets Cash 343.556 69 2012 Assets Treasury bills 343.556 70 2012 Assets Cash and Short Term funds 687.111 71 2012 Assets Collateral by Third Parties 343.556 72 2012 Assets Operating account with Central Banks 343.556 73 2012 Assets Cash reserves with Central Banks 343.556 74 2012 Assets Overnight placements 343.556 75 2012 Assets Eskrow with Central Banks 343.556 76 2012 Assets Banks within Nigeria 343.556 77 2012 Assets Banks outside Nigeria 343.556 78 2012 Assets Inter-bank placements 343.556 79 2012 Assets Provision for doubtful balances 343.556 80 2012 Assets Federal Government of Nigeria bonds 343.556 81 2012 Assets Due from banks 2748.44 82 2012 Assets Listed equity securities 343.556 83 2012 Assets Other liquid assets 343.556 84 2012 Assets Dealing securities 687.111 85 2012 Assets Overdrafts 343.556 86 2012 Assets Term loans 0 87 2012 Assets Mortgage loans 0 88 2012 Assets Commercial paper 0 89 2012 Assets Originated by the bank 343.556 90 2012 Assets Other 0 91 2012 Assets Loan loss provision 0 92 2012 Assets Loans and advances 0 93 2012 Assets Interest in suspence 0 94 2012 Assets Advances under finance lease 0 95 2012 Assets Other assets 0 96 2012 Assets Investment securities 0 97 2012 Assets Investment in subsidiary 0 98 2012 Assets Insurance assets 0 99 2012 Assets Other facilities 0 100 2012 Assets Goodwill 0 101 2012 Assets Defferes tax assets 0 102 2012 Assets Property and equipment 0 103 2012 Assets total Assets 4809.78 to Item 2012 2011 2010 2009 2008 2007 2006 ... Cash 0 0 0 0 0 0 0 Treasury bills 0 0 0 0 0 0 0 . . . I need to do pivot it and display using PHP as all possible ways to do the using Mysql was unsuccessful Similar TutorialsI'm having trouble with the logic for my query. Printing $sql gives me the following: SELECT r.id,r.created,r.firstname,r.lastname,r.address1,r.city,r.state,r.zip,r.phone,r.email,r.comments,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 'BBQ' at line 1 "BBQ" is the first $val from the loop. What am I missing? Code: [Select] $sql = "SELECT r.id,r.created,r.firstname,r.lastname,r.address1,r.city,r.state,r.zip,r.phone,r.email,r.comments,"; $dbQuery = mysql_result(mysql_query("SELECT description FROM events"), 0,0); $size = count($dbQuery); $i = 0; foreach ($dbQuery as $val) { //02 if($val != '') { //02 $sql .= "MAX(CASE e.description WHEN '". $val ."' THEN e.description END) AS `". $val ."`"; } $i++; if($i != $size) $sql .= ","; }//02 if (!mysql_query($dbQuery)) { die('Error: ' . mysql_error()); } $sql .= "FROM requests r LEFT OUTER JOIN ( registration_xref xref INNER JOIN events e ON e.id_events = xref.event_id ) ON r.id = xref.attendee_id GROUP BY r.id"; Hi All I have been trying to solve my query problem for days with no luck. Please help I'm trying to build a pivot table query from 2 part query in php. Both query runs ok in mysql query browser but once I place the query into php it doesn't work. Here is the my code I have a table I want to turn into a pivot table +------+---------+-------------+--------+ | id | product | salesperson | amount | +------+---------+-------------+--------+ | 1 | radio | bob | 100.00 | | 2 | radio | sam | 100.00 | | 3 | radio | sam | 100.00 | | 4 | tv | bob | 200.00 | | 5 | tv | sam | 300.00 | | 6 | radio | bob | 100.00 | +------+---------+-------------+--------+ //Building First Part of the Query to get all the columns $sql = "SELECT DISTINCT CONCAT(',SUM(IF(salesperson = "',salesperson,'",1,0)) AS `',salesperson,'`') AS countpivotarg FROM sales WHERE salesperson IS NOT NULL "; $result = $mdb2->query($sql); $count = $result->numRows(); if ($count>0){ while ($row = $result->fetchRow()){ $q .= $row['countpivotarg'] } } /*This should give the following results to plug into the next query statement*/, ,SUM(IF(salesperson = "bob",1,0)) AS `bob` ,SUM(IF(salesperson = "sam",1,0)) AS `sam` //Plug first query results into a second query statement// $sql = 'SELECT product ' . $q . ',COUNT(*) AS Total FROM sales GROUP BY product WITH ROLLUP;'; $result = $mdb2->query($sql); $count = $result->numRows(); if ($count>0){ while ($row = $result->fetchRow()){ echo "rows here"; } } //Results +---------+------+------+-------+ | product | bob | sam | Total | +---------+------+------+-------+ | radio | 2 | 2 | 4 | | tv | 1 | 1 | 2 | | NULL | 3 | 3 | 6 | +---------+------+------+-------+ Any help would be greatly appreciated. Thanks 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) hello freinds, iam new to this forum and new to php. i have just finished a course on html and css. but now i have realised that its become comulsory for me to learn php. but iam confused as to what ellements of php i should learn. since this is a programming language i find its gonna be real tuff for me, can u please advise as to which parts i should learn first to get going. i wont be developing custom websites for customers, just for my own hobbies. my main ambition is to utilise API's in my websites. Thanx Hello, I have been trying to come up with bar codes in php to represent integers for example 7432973853. How do I get a bar code for a particular string of numbers? Well my forum code is almost complete and one feature it is lacking is BB Codes. How would I go about making BB Codes for the following: Code: [Select] [URL=Url]Title[/URL] [IMG]Img Url[/IMG] [b]bold[/b] [i]Italics[/i] [u]Underline[/u] [s]Strike-through[/s] These are the only ones I want atm so any help would be appreciated. Hey eveyone, I have this great idea I would love to do but I have some questions and whenever I have questions I come here and they always get answered :-) Anyway what do you guys know about SMS short codes (you know those commercials on TV that are like text ringtone1 to 555)? I did some quick reading and it seems they are super expensive?!? Is there anyway to get them free or cheaper if you can write your own code? Has anyone had any experience with sms short codes? Also is there anyway for a site to receive a SMS and then charge that persons service provider with php? Thanks Hellow every one; i need little help to change code in my script version as i have older version codes and em using new version script so i need to change codes on my version script. iam using latest script which codes are these Quote if ($CURUSER['control_panel'] == 'yes' && $privacylevel == 'strong') print('<td class="f-border comment-details" align="left" width="15%"><center><strong>'.$postername.'</strong><br /><i>'.$title.'</i><br /><img width="80" height="80" src="'.$avatar.'"><br /><br />Uploaded: ---<br />Downloaded: ---<br />Ratio: ---<br /><br /><a href="account-details.php?id='.$row["user"].'"><img src="themes/'.$THEME.'/forums/icon_profile.gif"></a> <a href="mailbox.php?compose&id='.$row["user"].'"><img src="themes/'.$THEME.'/forums/icon_pm.gif" ></a></center></td>'); and Quote else print('<td class="f-border comment-details" align="left" width="15%"><center><strong>'.$postername.'</strong><br /><i>'.$title.'</i><br /><img width="80" height="80" src="'.$avatar.'"><br /><br />Uploaded: '.$useruploaded.'<br />Downloaded: '.$userdownloaded.'<br />Ratio: '.$userratio.'<br /><br /><a href="account-details.php?id='.$row["user"].'"><img src="themes/'.$THEME.'/forums/icon_profile.gif"></a> <a href="mailbox.php?compose&id='.$row["user"].'"><img src="themes/'.$THEME.'/forums/icon_pm.gif" ></a></center></td>'); and i want to change them under Quote if ($row["class"] == 3 || $row["donated"] > 0) { if ($site_config["donatedstar"] || $site_config["vipstar"]) { $star = "<img src=images/star.gif border=0>"; } else { $star = ""; } } if ($privacylevel != "strong" || ($CURUSER["control_panel"] == "yes")) { print("<tr><td valign=top width=150 align=left class=table_col2><center><b>$postername $star</b><br><i>$title</i></center><br>Uploaded: $useruploaded<br>Downloaded: $userdownloaded<br>Ratio: $userratio<br><br><center><img width=80 height=80 src=$avatar></center><br></td>"); and Quote }else{ print("<tr><td valign=top width=150 align=left class=table_col2><center><b>$postername $star</b><br><i>$title</i></center><br>Uploaded: ---<br>Downloaded: ---<br>Ratio: ---<br><br><center><img width=80 height=80 src=$avatar></center><br></td>"); } and one more thing i have two different images for both classes as in older codes version only one image specify for both class but em using two images for "Donated" = start.gif and for "VIP" = vip.gif so i want both images for both class so pls help me how to do them. Thanks, Regards. Hi, below is the output from FMS streams in XML format. and there is no way i can change the format of XML file, how do it just get just codes( like 20p5tc0bqm25an2, 42fem6h84kq3nmb, etc) Thank you <?xml version="1.0" encoding="utf-8" ?> - <result> <level>status</level> <code>NetConnection.Call.Success</code> <timestamp>8/25/2010 2:51:34 AM</timestamp> <name>_defaultRoot_:_defaultVHost_:::_1</name> - <data> <_0>20p5tc0bqm25an2</_0> <_1>42fem6h84kq3nmb</_1> <_2>b2zudqmrfqa05a6</_2> <_3>bsbzrnde1z4qmdv</_3> <_4>cgpm8wlvzyrp6b7</_4> <_5>hqy95ympgq1t5ui</_5> <_6>qa8ultkr5pv2vas</_6> <_7>qr94iqvdqag26ee</_7> <_8>wjesw80adgv4usu</_8> <_9>zcdua1yok7kw5l4</_9> </data> </result> This topic has been moved to Miscellaneous. http://www.phpfreaks.com/forums/index.php?topic=317279.0 I am not sure what is going wrong but I have not changed any of my codes and now I am getting this error message on a simple form that I am testing. Any idea what has happened. I have had several successful tries that have updated to the db. Quote Warning: mysql_query() [function.mysql-query]: Access denied for user 'ebermy5'@'localhost' (using password: NO) in /home/ebermy5/public_html/form.php on line 20 Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in /home/ebermy5/public_html/form.php on line 20 Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/ebermy5/public_html/form.php on line 21 Warning: mysql_query() [function.mysql-query]: Access denied for user 'ebermy5'@'localhost' (using password: NO) in /home/ebermy5/public_html/form.php on line 29 Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in /home/ebermy5/public_html/form.php on line 29 This is the form Code: [Select] <?php @include_once ("Connections/connect_to_mysql.php") or die('could not connect to database'); $err=''; if($_POST["submit"]){ // Validate form data if($_POST["firstname"]=='') $err.='Please enter First Name<br>'; if($_POST["email"]=='') $err.='Please enter Email<br>'; if($err==''){ // Check if there are duplicate entries in the 'contacts' table $results = mysql_query("SELECT id FROM `Members` WHERE firstname='".addslashes($_POST["firstname"])."' and Email='".addslashes($_POST["email"])."'"); if($row = mysql_fetch_array($results)){ $err.='Can not add duplicate entry<br>'; } else{ // adding new record to 'contacts' table mysql_query("INSERT INTO Members (firstname,lastname,country,Email) values ('".addslashes($_POST["firstname"])."','".addslashes($_POST["lastname"])."','".addslashes($_POST["country"])."','".addslashes($_POST["email"])."')"); // redirecting to success screen exit; } } } ?>The HTML below.. Code: [Select] <html> <head> <title>Add New Contact</title> </head> <body> <h2>Register with us</h2> <?php echo $err==''?'':('<p style="color:red;">'.$err.'</p>') ?> <form method="post" action="form.php"> <table border="0"> <tr> <td valign="middle">First Name:</td> <td><input type="text" name="firstname" size="30" value="<?php echo htmlspecialchars($firstname) ?>"></td> </tr> <tr> <td valign="middle">Last Name:</td> <td><input type="text" name="lastname" size="30" value="<?php echo htmlspecialchars($lastname) ?>"></td> </tr> <tr> <td valign="middle">Country:</td> <td><input type="text" name="country" size="30" value="<?php echo htmlspecialchars($country) ?>"></td> </tr> <tr> <td valign="middle">Email:</td> <td><input type="text" name="email" size="30" value="<?php echo htmlspecialchars($email) ?>"></td> </tr> </table><br> <input type="submit" name="submit" value=" Submit! "> </form> </body> </html> Hello everyone, Can somebody help me? I am a novice in PHP programming. I do not understand a line of code below: $action = isset($_GET['action']) ? $_GET['action'] : ''; The variables $action saves the values obtained by the GET method, but what does this piece of code ? $_GET['action'] : ''; Thanks in advance! Hi everyone,I want to learn PHP hook and I try hook examples.I tired this example but I saw only white page.It has a problem but I can't understand it Codes; hook.php <?php class Hooks { public function add($hook,$class=null,$method=null,$args=null) { if (empty($method)) error("You must include a method (function) when defining add_hook."); $this->hooks[$hook][]=array((!empty($class)?array($class,$method):$method),$args); return $this; } public function clear($hook=null) { if (!empty($hook)) unset($this->hooks[$hook]); else $this->hooks=null; } public function run($hook) { if (empty($this->hooks[$hook])) return $this; foreach ($this->hooks[$hook] as $hkey=>$hvalue) { if (is_array($hvalue[0])) $hvalue[0]=array($hvalue[0][0],$hvalue[0][1]); if (!empty($hvalue[1])) if (!is_array($hvalue[1])) call_user_func($hvalue[0],$hvalue[1]); else call_user_func_array($hvalue[0],$hvalue[1]); else call_user_func($hvalue[0]); } return $this; } } ?> script.php <?php // For a function: function doTitle() { echo $current->title; } $hooks->add('head_title',null,'doTitle'); // For a class: class Output { function body_final() { echo implode("\n",$this->body); } } $hooks->add('page_body','Output','body_final'); // To pass multiple arguments into a function, you must use: function test($arg1,$arg2) { echo "Arg1: {$arg1}, Arg2: {$arg2}"; } $hooks->add('page_body',null,'test',array('test','ing')); ?> index.php <?php // You must include the hook file here, or you can make an includes file to do all of your startup (as is normally done). include 'hook.php'; include 'script.php'; ?><!DOCTYPE html> <html> <head> <?php run('head_meta'); ?> <title><?php $hooks->run('head_title'); ?></title> <?php $hooks->run('head_links'); $hooks->run('head_scripts'); ?> </head> <body> <?php $hooks->run('page_body'); ?> </body> </html> Thank you I am using a sloppy 3rd party API that doesn't return error codes, just error messages. See below Code: [Select] $err = $object->getErrorMessage(); if( strlen($err) > 0 ) { header("Location: step1.php?err=$err"); } As you can see this is sloppy because anyone can just modify the address bar of step1.php and inject their own error message, or javascript or their own HTML buttons, etc. As a work around, I thought maybe I could use POST instead of GET... At least then the "err" variable would be hidden for the most part Any ideas how I can work around this? Thanks! Hi, I have a question. Currently, when I retrieve the records in my zone 'North', there is no misalignment. However when I tried to copy the same coding to make up zone 'West', the alignment went off? May I know what is the reason? Any advice to align it properly. Attached is the picture of my example <div id ="location"> <input name="zone[]" type="checkbox" id="1" value="1"> <label for="1">North</label><br /> <?php $dbc = mysqli_connect('localhost', 'aliendatabase', '1234567', 'aliendatabase') or die(mysqli_error()); $query = ("SELECT * FROM tutor_preferred_district ORDER BY district_id ASC"); $sql = mysqli_query($dbc, $query) or die(mysqli_error()); echo'<table><tr>'; // Start your table outside the loop... and your first row $count = 0; // Start your counter while($data = mysqli_fetch_array($sql)) { /* Check to see whether or not this is a *new* row If it is, then end the previous and start the next and restart the counter. */ if ($count % 3 == 0) { echo "</tr><tr>"; $count = 0; } echo '<td><input name="district[]" type="checkbox" id="'.$data['district_id'].'" value="'.$data['district_id'].'">'; echo '<label for="'.$data['district_id'].'">'.$data['district_name'].'</label></td>'; $count++; //Increment the count } echo '</tr></table><br />'; //Close your last row and your table, outside the loop ?> <input name="zone[]" type="checkbox" id="2" value="2"> <label for="2">West</label><br /> <?php $dbc = mysqli_connect('localhost', 'aliendatabase', '1234567', 'aliendatabase') or die(mysqli_error()); $query = ("SELECT * FROM tutor_preferred_district ORDER BY district_id ASC"); $sql = mysqli_query($dbc, $query) or die(mysqli_error()); echo'<table><tr>'; // Start your table outside the loop... and your first row $count = 0; // Start your counter while($data = mysqli_fetch_array($sql)) { /* Check to see whether or not this is a *new* row If it is, then end the previous and start the next and restart the counter. */ if ($count % 3 == 0) { echo "</tr><tr>"; $count = 0; } echo '<td><input name="district[]" type="checkbox" id="'.$data['district_id'].'" value="'.$data['district_id'].'">'; echo '<label for="'.$data['district_id'].'">'.$data['district_name'].'</label></td>'; $count++; //Increment the count } echo '</tr></table>'; //Close your last row and your table, outside the loop ?> </div> I need to echo out a database value that contains an embed code for soundcloud (music hosting site), into a value of a text input. How do I do this without the object getting rendered? This is an example of an embed code. <object height="81" width="100%"> <param value="http://player.soundcloud.com/player.swf?url=http%3A%2F%2Fsoundcloud.com%2Fgeneralpie%2Fdukebox001&secret_url=false" name="movie"> <param value="always" name="allowscriptaccess"> <embed height="81" width="100%" type="application/x-shockwave-flash" src="http://player.soundcloud.com/player.swf?url=http%3A%2F%2Fsoundcloud.com%2Fgeneralpie%2Fdukebox001&secret_url=false" allowscriptaccess="always"> </object> The value has to stay exactly the same so that it can pass a regular expression for validation. Is this possible? I am letting users select there color code option for text-shadow for there username I am storing them into a field called "color" HEX exaMPLE: FF6633,AA3633 First is the background color, 2nd is the foreground. (then i'll explode them) Is there a PHP function that let's me validate the input so it's HEX ONLY? I will be db escaping it and intval it too. I would like to have a script that would check certain parameters and if none of them returns TRUE, execute a set of codes. So the idea is somehow like if...else(). Code: [Select] <?php // trying to put if() directly within if() if ( // checking of variables if (!isset($transaction)) { echo '<font color="#FF0000">Type of transaction is not set.</font>'. '<br />'; } else { if ($transaction == 'sell') { if (empty($BV)) { echo '<font color="#FF0000">Book value is not set.</font>'. '<br />'; } } } if (empty($stock)) { echo '<font color="#FF0000">Stock symbol is not set.</font>'. '<br />'; } if (empty($price)) { echo '<font color="#FF0000">Price per share is not set.</font>'. '<br />'; } if (empty($volume)) { echo '<font color="#FF0000">Volume of shares is not set.</font>'. '<br />'; } ) { echo 'Go back and fill out necessary fields.'; } // if none of the above parameters returns TRUE else { echo 'Copy and paste the following in the email that you would be submitting.' </tr> <tr> <td> switch ($transaction) { case 'buy': include('calc_buy.php'); break; case 'sell': include('calc_sell.php'); break; default: echo '<font color="#FF0000"><b>If you see this message there is something wrong with your session. Please send us a feedback with the details of your session before seeing this message.</b></font>'; } } ?> This code returns an error and as I understand the error, it is because of if (if ()). How can I make it work? Thanks in advance! Hello. i have created a template in dreamweaver, however when i added php code to a page the position of my bottom div got messed up... it works 100% with just text. I have a container div with other divs in it. inside the container div i have a div named content, and this div is the only div which is editable. in the bottom (outside of the container div) i have a bottom div which is centered with margin:0 auto; this divs position gets totally messed up once i add php code in the content div. http://elevweb.skit.no/0111nema/action_adventure.php here is the link to the page with php with it, as u can see the div with facebook etc in it got a messed up position. |