nebrer Posted September 17, 2015 Share Posted September 17, 2015 I have a owners registry database for motorcycles ( vintage ) Database name: bikes Tables : owner & modellerColumns in owner: mcColumns in modeller: modell and antal Today the owner can type in their model and that ends up in table "owner" column "mc" I have a table ( modeller ) with produced number of each model In column "modell" are the different modelsIn column "antal" are the produced number of each model Today I can present how many of each model there are in the database. I wish to calculate how many of each model we have in the database compared to produced. Ie: We have 100 pcs B50 ( 20% ) of produced 500 pcs in the database We have 100 pcs B25 ( 10% ) of produced 1000 pcs in the database Todays code that shows how many of every different models we have Rickard Beginner from Sweden Sorry English is not my first language. <?php ## databas koppling! include ('mysqlcondb.php'); // Make a MySQL Connection { $query = mysql_query("SELECT * FROM owner "); if (!$query) die(mysql_error()); $numrows = mysql_num_rows($query); echo "<br />Here are <b>"; echo $numrows; echo "</b> Bikes in owners database <br><br>"; } $query = "SELECT *, COUNT(mc) FROM owner GROUP BY mc"; $result = mysql_query($query) or die(mysql_error()); // Print out result while($row = mysql_fetch_array($result)){ echo "There are ". $row['COUNT(mc)'] ." ". $row['mc'] .""; echo "<br />"; } ?> <?php ## close connection! mysql_close($connect);?> Quote Link to comment Share on other sites More sharing options...
Barand Posted September 17, 2015 Share Posted September 17, 2015 (edited) this will give all models and their % in the database SELECT m.modell , m.antal , o.total , ROUND(o.total/m.antal*100, 1) as pc FROM modeller m LEFT JOIN ( SELECT mc as modell , COUNT(*) as total FROM owner GROUP BY modell ) o USING (modell) ORDER BY modell sample output +--------+-------+-------+------+ | modell | antal | total | pc | +--------+-------+-------+------+ | A50 | 20 | NULL | NULL | | B25 | 50 | 6 | 12.0 | | B40 | 50 | 6 | 12.0 | | B50 | 100 | 4 | 4.0 | | VH500 | 20 | 6 | 30.0 | +--------+-------+-------+------+ Edited September 17, 2015 by Barand Quote Link to comment Share on other sites More sharing options...
nebrer Posted September 17, 2015 Author Share Posted September 17, 2015 Thanks Barand. As I wrote before " I´m a beginner" and don´t realy get where I should insert the code. I need a little more guidance. Sorry. Rickard Quote Link to comment Share on other sites More sharing options...
Barand Posted September 17, 2015 Share Posted September 17, 2015 execute the query and list the results. (I have use mysqli - which you should be using (or PDO) and not the deprecated mysql_ library) $mysqli = new mysqli(HOST,USERNAME,PASSWORD,'bikes'); $sql = "SELECT m.modell , m.antal , o.total , ROUND(o.total/m.antal*100, 1) as pc FROM modeller m LEFT JOIN ( SELECT mc as modell , COUNT(*) as total FROM owner GROUP BY modell ) o USING (modell) ORDER BY modell"; $res = $mysqli->query($sql); // execute the query while (list($model, $antal, $total, $pc) = $res->fetch_row()) { // list the results echo "We have $total pcs $model ( {$pc}% ) of produced $antal pcs in the database<br>"; } Quote Link to comment Share on other sites More sharing options...
nebrer Posted September 17, 2015 Author Share Posted September 17, 2015 Thanks again. I will try tomorrow. Have a nice weekend Rickard Quote Link to comment Share on other sites More sharing options...
nebrer Posted September 18, 2015 Author Share Posted September 18, 2015 Ohh. Many years since I did any programming and I did´t know about mysqli. Quote Link to comment Share on other sites More sharing options...
nebrer Posted September 18, 2015 Author Share Posted September 18, 2015 Can´t you use include ('mysqlcondb.php'); I tryed this and of course it did´t work. <html> <head> <TITLE>database</TITLE> <link href="style1.css" rel="stylesheet" type="text/css" media="screen" /> </head> <body><center> <div id="latest-post-wrap"> <div id="latest-post" class="post"> <p class="byline"></p> <h1 class="title"><a href="#">database</a></h1> <div class="entry"> </center> <?php $mysqli = new mysqli(myhost,myusernamne,mypassword,'bikes'); // Make a MySQL Connection $sql = "SELECT m.modell , m.antal , o.total , ROUND(o.total/m.antal*100, 1) as pc FROM modeller m LEFT JOIN ( SELECT mc as modell , COUNT(*) as total FROM owner GROUP BY modell ) o USING (modell) ORDER BY modell"; $res = $mysqli->query($sql); // execute the query while (list($model, $antal, $total, $pc) = $res->fetch_row()) { // list the results echo "We have $total pcs $model ( {$pc}% ) of produced $antal pcs in the database<br>"; } ?> <?php ## close connection! mysql_close($connect);?> test </html> Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted September 18, 2015 Solution Share Posted September 18, 2015 myhost, myusernamne, mypassword Those need to be variables ($ prefixes) or you need to have defined them as constants Can´t you use include ('mysqlcondb.php'); of course you can. I didn't because I wanted to show a mysqli connection I normally use an included file in which I define HOST, USERNAME, PASSWORD and DATABASE Quote Link to comment Share on other sites More sharing options...
nebrer Posted September 18, 2015 Author Share Posted September 18, 2015 I got it now. Thank you so much. I changed it to: $mysqli = new mysqli("myhost","myusernamne","mypassword",'bikes'); Now I can carry on building Have a great weekend. Rickard Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.