Search the Community

how can i get a table like so: from a database table with teams and goals scored Season 2016/17 Premier League 2016/17 Team Information Home Away Total Points Information P W D L F A W D L F A W D L F A +/- Pts Manchester City 7 3 0 0 9 2 3 0 1 9 5 6 0 1 18 7 11 18

how can i leave out all the null rows from this result of query below;iam trying to get the list of player names and their playing position mysql> select f0.player_id as P_id,concat (f0.Fname,' ',f0.Lname)as pname,f1.position_id,f2.position from( select * from Soka_player_details_tbl) as f0 left join (select * from Soka_players_team_tbl where Soka_players_team_tbl.SeasonID=(select SeasonID from tbl_season where Publish='1')and Team_catId= '1' )as f1 on f1.player_id=f0.player_id left join (select * from Soka_position_tbl )as f2 on f1.position_id=f2.position_id order by position_id asc; +------+----------------------------------------------------+-------------+--------------+ | P_id | pname | position_id | position | +------+----------------------------------------------------+-------------+--------------+ | 5 | Performance nutritionist Assistant manager | NULL | NULL | | 8 | Kit manager Equipment manager | NULL | NULL | | 6 | Head of performance Head of medical services | NULL | NULL | | 4 | Masseur Assistant kit manager | NULL | NULL | | 7 | Assistant fitness coach Under-21s assistant coach | 1 | Goal keepers | | 1 | Manager First-team coach | 1 | Goal keepers | | 2 | Goalkeeping coach Fitness coach | 2 | Defenders | | 3 | Club doctor First-team physiotherapist | 3 | Midfielders | | 9 | Football analyst Academy manager | 4 | Forwards | +------+----------------------------------------------------+-------------+--------------+ 9 rows in set (0.00 sec)

how can I get a full table from this result set +-----------+-----------+------+------+--------------+------+--------------+ | HomeScore | AwayScore | g_id | a_id | h_name | h_id | a_name | +-----------+-----------+------+------+--------------+------+--------------+ | 0 | 0 | 11 | 6 | Seowa fc | 7 | Seowa U-14 | | 0 | 0 | 12 | 7 | Seowa U-14 | 6 | Seowa fc | | 0 | 0 | 13 | 8 | Teowa fc | 6 | Seowa fc | +-----------+-----------+------+------+--------------+------+-------------

<?php $host= "localhost"; $username="root"; $password="root"; $db_name= "test2.0"; $tbl_name="permohonan"; mysql_connect("$host","$username","$password")or die ("cannot connect"); mysql_select_db ("$db_name")or die("cannot select DB"); if(isset($_POST['submit'])) { $Jabatan = mysql_real_escape_string($_POST['Jabatan']); $unit = mysql_real_escape_string($_POST['unit']); $lain2 = mysql_real_escape_string($_POST['lain2']); $nama_pemohon = mysql_real_escape_string($_POST['nama_pemohon']); $destinasi = mysql_real_escape_string($_POST['destinasi']); $tujuan = mysql_real_escape_string($_POST['tujuan']); $maklumat_ = mysql_real_escape_string($_POST['maklumat_']); $datedepart_= mysql_real_escape_string($_POST['datedepart_']); $timedepart_= mysql_real_escape_string($_POST['timedepart_']); $datearrive_ = mysql_real_escape_string($_POST['datearrive_']); $timearrive_ = mysql_real_escape_string($_POST['timearrive_']); $query1 = mysql_query("INSERT INTO permohonan VALUES(NULL,'$Jabatan','$unit','$lain2','$nama_pemohon','$destinasi','$tujuan','$maklumat_','$datedepart_','$timedepart_,'$datearrive_',$timearrive_')"); if($query1) { header("location : reservationform.php"); } } ?>

Hi again, Hopefully the last question as I am not 100% how to solve this one. So on my website someone carried out a search from a search bar using the 'POST' method , teh search results show all whiskies in the databse. I have a number of whiskies with the same name but with different dates and prices. I would like it just to show one of each type that was searched for rather than all of them. I have attahced a clip of the databse. Thanks Index.php </head> <?php $page='index'; include('header.php'); include('navbar.php'); ?> <script type="text/javascript"> function active(){ var search_bar= document.getElementById('search_bar'); if(search_bar.value == 'Search for your whisky here'){ search_bar.value='' search_bar.placeholder= 'Search for your whisky here' } } function inactive(){ var search_bar= document.getElementById('search_bar'); if(search_bar.value == ''){ search_bar.value='Search for your whisky here' search_bar.placeholder= '' } } </script> <body> <div class="third_bar"> <div class="background_image"> </div> <div class="form"><form action= "search.php" method="post"> <input type="text" name="search" id="search_bar" placeholder="" value="Search for your whisky here" max length="30" autocomplete="off" onMouseDown="active();" onBlur="inactive();"/><input type="submit" id="search_button" value="Go!"/> </form> </div> </div> </body> </div> <?php include ('footer.php'); ?> Search.php <?php $page='search'; include('header.php'); include ('navbar.php'); echo "<br>"; include ('connect.php'); if (isset ($_POST['search'])) { //the 'search' refers to the 'search' name=search on the index page and makes does something when the search is pushed. $search = $_POST['search']; $search = "%" . $search . "%"; // MySQL wildcard % either side of search to get partially matching results // No wildcard if you want results to match fully } else { header ('location: index.php'); } $stmt = $conn->prepare("SELECT * FROM test_db WHERE name LIKE :name ORDER BY name ASC"); // Use = instead of LIKE for full matching $stmt->bindParam(':name', $search); $stmt->execute(); $count = $stmt->rowCount(); // Added to count no. of results returned if ($count >= 1) { // Only displays results if $count is 1 or more echo "<div class='results_found'>"; echo $count; echo " results found<br>"; echo "</div>"; while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo "<div class='results'>"; echo "<div class='result_name'>"; echo "<b>Whisky Name:</b><br>"; echo "<a href='details1.php?id={$row['lot_id']}' >{$row['name']}</a>"; echo"<br>"; echo "</div>"; echo "</div>"; } } else { echo " Sorry no records were found"; } ?> </htm

Hi I have been working on my OOP and have put together some class files to aid my test application ( photo album ) on the upload page I have the browse box, a caption text box and an upload button this page posts to self, Once you click on upload it is also supposed to insert a database entry to allow tracking of the file's attributes, once I click the upload button I get this error message back, " Database Query Failed: Incorrect integer value ' ' for column 'id' at row 1 " so I have re looked over my codes in regards to uploading files and just can not seem to put my mouse on the spot that's causing me an issue so here is the codes that matter to the file uploads... this one is from my database.php class file, public function insert_id() { // get the last id inserted over the current db connection return mysql_insert_id($this->connection); } and this one is one comes from my photograph class file, public function create() { global $database; $attributes = $this->sanitized_attributes(); $sql = "INSERT INTO ".self::$table_name." ("; $sql .= join(", ", array_keys($attributes)); $sql .= ") VALUES ('"; $sql .= join("', '", array_values($attributes)); $sql .= "')"; if($database->query($sql)) { $this->id = $database->insert_id(); return true; } else { return false; } } Looking at my error and the information in those functions I can guess that's where the issue is coming from just don't get why any ideas please?

I want to display 10 products from each category from database, I started to display the categories but how to make displaying the products from each one. Here is the code I make so far. I'm not sure am I doing it right with doble sql select , or it can be done only with one. <div class="inner shadow"> <?php $query = 'SELECT id, title_bg AS `title_cat` FROM categories'; $result = $this->db->query($query); ?> <?php foreach ($result->result() as $row): ?> <?php $title_cat = stripslashes($row->title_cat); ?> <div class="prod-sec"> <div class="prod-head"> <h2><?= $title_cat?></h2> <div class="clear"></div> </div> <?php $query1 = 'SELECT t1.id, t1.title_bg AS `Title`, t1.text_bg AS `Text`, t1.price, t1.discount, t1.category_id, t1.promo_page, t2.id AS FileID, t2.ext, FROM products t1 LEFT JOIN products_pictures t2 ON t1.id = t2.object_id LEFT JOIN categories t3 ON t3.id = t1.category_id WHERE t1.promo_page = 0 AND t1.is_active = 1 AND t3.title_bg = '$title_cat' ORDER BY RAND() LIMIT 10'; $result1 = $this->db->query($query1); ?> <div id="classeslist2"> <ul class="home_middle_products"> <?PHP foreach($result1->result() as $row1) { $f = 'files/products_first_page/' . $row1->id . '.jpg'; if(is_file(dirname(__FILE__) . '/../../' . $f)) { $img = site_url() . "files/products_first_page/".$row1->id.".jpg"; } else { $img = site_url() . "files/products/".$row1->id."/".$row1->FileID."_2.".$row1->ext; } $title = stripslashes($row1->Title); $text = character_limiter(strip_tags(stripslashes($row1->Text)),250); $title_url = getLinkTitle($title); $link = site_url()."products/product/".$row1->id."/{$title_url}"; ?> <li style="width: 185px; height: 270px; margin-left: 3px; margin-top: 10px;"> <div class="thumb"> <a href="<?=$link?>"><img src="<?=$img?>" alt="<?=$title?>" width="182" /></a> <div class="price"><?PHP echo product_price($row1, array('show_discount' => false,"show_old_price"=>false, 'show_label' => false, 'view' => 'no')); ?> </div> </div> <h2></h2> <h2><a href="<?=$link?>"><?=$title?></a></h2> <? $text = substr($text, 0, 100); ?> <? if (strlen($text) == 100) $text .= '...' ; ?> <p><?=$text?></p> </li> <?php } ?> <div class="clear"></div> </ul> </div> </div> <?php endforeach; ?> <div class="clear"></div> </div>

I am wanting to combine the following two query's into a single db hit. $db->query("SELECT id, count(*) total, sum(case when type = 'Leveler OTH' then 1 else 0 end) LevOTH, sum(case when type = 'Mugger OTH' then 1 else 0 end) MugOTH, sum(case when type = 'Buster OTH' then 1 else 0 end) BustOTH FROM othwinners WHERE userid = ?"); $db->execute([$profile->id]); $db->query("SELECT id, count(*) total, sum(case when type = 'Leveler OTD' then 1 else 0 end) LevOTD, sum(case when type = 'Mugger OTD' then 1 else 0 end) MugOTD, sum(case when type = 'Buster OTD' then 1 else 0 end) BustOTD, sum(case when type = 'Mobster OTD' then 1 else 0 end) MobOTD, FROM otdwinners WHERE userid = ?"); $db->execute([$profile->id]); The two on there own work perfect but I am unsure if using INNER JOIN or another JOIN method is going to get me what I am looking for. Both tables have the exact same columns the only difference's are in come cases the $profile->id may or may not exist and if not I will sort those with !num_rows() and the conditions for type change from one table to the other. Columns are id, userid, type, howmany, and timestamp...

update super set `name` = REPLACE(`name`, ',' , ' ') I have the following code that removes the comma in a MySQL database column & replaces it with a blank. In the above example, the database is named super & the column is named name. This code works great but currently I'm running the script each day & changing it to also remove periods, question marks, etc. Is there a way I can edit the above code that will not only find & replace the , with a blank space, but edit it so it will find the periods, commas, question mark, exclamation mark, etc all in one run? Please help as I am currently editing the above code to numerous other things to find & replace daily. Thank

I have seen a lot of demos or sort of the same questions with the Select All option. But what I want is to just have a drop down that will allow me to select All option and not showing the entire Select box. Individual Select option works but not when I tried to use ALL as an option. Here is my sample HTML: <select name="status" id="status" style="width: 224px;"> <option value="" selected="selected">Please select...</option> <option value="All">All</option> <option value="Option1">Option1</option> <option value="Option2">Option2</option> <option value="Option3">Option3</option> <option value="Option4">Option4</option> </select> From the code above I would want to filter the result using the All option. So this is the page is where I filter and show the results in a table, <script> $(document).ready(function(){ $("#results").show(); }); </script> <script type="text/javascript"> $(document).ready(function(){ $("#RetrieveList").on('click',function() { var status = $('#status').val(); var date = $('#Date').val(); var date1 = $('#Date1').val(); $.post('retrieve_status.php',{status:status, date:date, date1:date1}, function(data){ $("#results").html(data); }); return false; }); }); </script> <form id="form2" name="form2" method="post" action=""> <table width="941" border="0" align="center"> <tr> <th width="935" colspan="9" scope="col">Status: <select name="status" id="status" style="width: 224px;"> <option value="" selected="selected">Please select...</option> <option value="All">All</option> <option value="Option1">Option1</option> <option value="Option2">Option2</option> <option value="Option3">Option3</option> <option value="Option4">Option4</option> </select> Start Date:<input type="text" name="Date" id="Date" size="8"/> End Date:<input type="text" name="Date1" id="Date1" size="8"/> <input name="action" type="submit" id="RetrieveList" value="Retrieve List" /> </th> </tr> </table> </form> <div id="results"> </div> And this is how I fetch the data, <?php require 'include/DB_Open.php'; $status = $_POST['status']; $date = $_POST['date']; $date1 = $_POST['date1']; if ($_POST['status'] == 'ALL') { $sql_status = '1'; } else { $sql_status = "status = '".mysql_real_escape_string($_POST['status'])."'"; } $sql="SELECT column1, column2, status FROM tracker WHERE status = '" . $sql_status . "' AND scheduled_start_date BETWEEN '" . $date . "' AND '" . $date1 . "' ORDER BY scheduled_start_date"; $myData = mysql_query($sql); //to count if there are any results $numrow = mysql_num_rows($myData); if($numrow == 0) { echo "No results found."; } else { echo "CRQ Count: $numrow"; } { echo "<table width='auto' cellpadding='1px' cellspacing='0px' border=1 align='center'> <tr> <th align='center'><strong>Column1</strong></th> <th align='center'><strong>Column2</strong></th> <th align='center'><strong>Status</strong></th> </tr>"; while($info = mysql_fetch_array($myData)) { echo "<form action='retrieve_status.php' method='post'>"; echo"<tr>"; echo "<td align='center'>" . $info['column1'] . "<input type=hidden name=column1 value=" . $info['column1'] . " </td>"; echo "<td align='center'>" . $info['column2'] . "<input type=hidden name=column2 value=" . $info['column2'] . " </td>"; echo "<td align='center'>" . $info['status'] . "<input type=hidden name=status value=" . $info['status'] . " </td>"; echo "</tr>"; echo "</form>"; } } echo "</table>"; include 'include/DB_Close.php'; ?>

Hey guys: I have a database that is full of items. Each item lives in a box, with various accessories. I'm trying to loop through this DB to print out a label for each case that shows the amount of items that are in the case, along with the accessories that belong to it. I currently have a rather convoluted bit of code, that kind of works, but doesn't have the order that I want. My result is currently this: 4 X Generic lighting hanging clamp 4 X Generic lighting hanging clamp 4 X Generic lighting hanging clamp 4 X Generic lighting hanging clamp 4 X Generic lighting hanging clamp 2 X 25kg Safety Bond 2 X 25kg Safety Bond 2 X 25kg Safety Bond 2 X 25kg Safety Bond 2 X 25kg Safety Bond 2 X 16a to Powercon 2 X 16a to Powercon 2 X 16a to Powercon 2 X 16a to Powercon 2 X 16a to Powercon ********** 12 X Generic lighting hanging clamp 6 X 25kg Safety Bond ********** All the information is there, but due to the way that it loops through it's in the wrong order. I'd like it to be grouped so it looks like this so that it is grouped by box: 4 X Generic lighting hanging clamp 2 X 25kg Safety Bond 2 X 16a to Powercon 4 X Generic lighting hanging clamp 2 X 25kg Safety Bond 4 X Generic lighting hanging clamp 2 X 16a to Powercon 4 X Generic lighting hanging clamp 2 X 25kg Safety Bond 2 X 16a to Powercon 4 X Generic lighting hanging clamp 2 X 25kg Safety Bond 2 X 16a to Powercon ********** 12 X Generic lighting hanging clamp 6 X 25kg Safety Bond ********** Here is the code that I'm using. At the moment I'm just concentrating on the if ($item_type == '2') part, but have shown the whole lot so you can see what I'm doing. foreach ($distinct_path as $path) { $sql = "SELECT * FROM current_items WHERE path = '$path'"; $result = $con->query($sql); if ($result->num_rows > 0) { // output data of each row while($row = $result->fetch_assoc()) { $item_name = $row['item_name']; $item_type = $row['item_type']; $item_qty = $row['qty']; $case_qty = $row['case_qty']; if ($item_type == '1') // THESE ARE MAIN ITEMS { $total_main_qty = $row['qty']; //main item QTY; $main_item_name = $item_name; } // This code will tell us number of full boxes, and number of extra lights left over $amtoffull = $total_main_qty/$case_qty; $amtoffull = floor($amtoffull); // we round down to the nearest whole number $total_left = $total_main_qty - ($case_qty * $amtoffull); if ($item_type == '2') // THESE ARE ACCESSORIES { $asc_qty = $row['qty']; //main item QTY; $asc_qty = $asc_qty/$total_main_qty; // THIS GIVES US THE TOTAL OF ONE Item $asc_qty = $asc_qty * $case_qty; // This is a full case amount //echo $asc_qty ." x ". $item_name ."</p>"; while ($i < $amtoffull) { echo $asc_qty ." X ". $item_name ."</p>"; ++$i; } } $i = 0; } echo "**********"; echo "<p>"; } I think I need to either shift the loops around so they work in a different order, or store everything into an array and then loop through that. Can any one help?

Hello! I need help making a query for this statement: Drinkers who frequent exactly one of the bars which Joe frequents The table looks like this: --------------------------------------------- | drinker | bar | --------------------------------------------- | Mike |A.P. Stump's | | Bob |Blue Angel | | Erik |Blue Angel | | Herb |Blue Angel | | Jesse |Blue Angel | | Joe |Blue Angel | | John |Blue Angel | | Justin |Blue Angel | | Mike |Blue Angel | | Rebecca |Blue Angel | | Tom |Blue Angel | | Vince |Blue Angel | | John |Cabana | | Mike |Cabana | | Vince |Cabana | | Joe |Caravan | | John |Caravan | | Tom |Caravan | | Bob |Coconut Willie's Cocktail Lounge | | Joe |Coconut Willie's Cocktail Lounge | | Rebecca |Coconut Willie's Cocktail Lounge | | Justin |Gecko Grill | | Rebecca |Gecko Grill | | Herb |Seven Bamboo | | Vince |Seven Bamboo | | Mike |The Shark and Rose | --------------------------------------------- I can't seem to understand the logic that is needed to build the query. I started with this: SELECT DISTINCT * FROM frequents F1 WHERE NOT EXISTS (SELECT * FROM frequents F2 WHERE F1.drinker = 'Joe' AND /* Something here */); The way I'm reading is that my NOT EXISTS sub query should have Drinkers who like SOME of the bars which Joe frequents then somehow turn that into only one. How should I proceed? Any help would be appreciated! Thanks in advance!

Hi Guys, I have a JET SQL query that i need to convert to MYSQL (Appologies too if this is posted in the wrong section.. i never know whether to go PHP or MYSQL!) Im not being lazy.. ive tried for hours but cannot get it to work, it has two depth inner join and a group by with a where (with 1 criteria)... If anyone can help id massively appreciate it and also explain how you got there... SELECT Count(tbl_Items.ItemID) AS CountOfItemID, tbl_LU_Collections.CollectionDesc FROM (tbl_Items LEFT JOIN tbl_LU_Categories ON tbl_Items.ItemCategory = tbl_LU_Categories.ItemCatID) LEFT JOIN tbl_LU_Collections ON tbl_LU_Categories.CollectionID = tbl_LU_Collections.CollectionID WHERE (((tbl_Items.RetailProduct)=-1)) GROUP BY tbl_LU_Collections.CollectionDesc;

how can I get the code of a function to a text file in mysql?how can i dump database with the stored functions too?

Hi I'm trying to run a cron here to remove profile pictures delete users delete private messages delete messages but I need some help to make my code more clean and effective <?php include("chat_code_header.php"); //// Delete profile pictures and users id older than 30 days//// $querybd = "SELECT Username, last_online, pprofilepic FROM Users2 WHERE last_online < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY))"; $resultbd = mysql_query($querybd) or die(mysql_error()); $users = array(); while($rowbd = mysql_fetch_array($resultbd)){ if ($rowbd['Username'] != '') $users[] = $rowbd['Username']; if($rowbd['pprofilepic'] == ""){}else{ $pprofilepic = realpath($rowbd['pprofilepic']); print "\n Pictures Removed:\n" . $pprofilepic . "\n"; unlink($pprofilepic); } } $list = implode('; ', $users); if($list == ""){printf ("Nobody Removed\n");}else{ print "\n Users Removed:\n" . $list . "\n"; } printf("Users Deleted: %d\n", mysql_affected_rows()); $sqldel = "DELETE Users2, StringyChat, pm, namechanges, kicksamount, broadcast, timeban FROM Users2 LEFT JOIN StringyChat ON Users2.mxitid = StringyChat.StringyChat_ip LEFT JOIN pm ON Users2.mxitid = pm.mxitid LEFT JOIN namechanges ON Users2.mxitid = namechanges.userid LEFT JOIN kicksamount ON Users2.mxitid = kicksamount.mxitid LEFT JOIN broadcast ON Users2.mxitid = broadcast.mxitid LEFT JOIN timeban ON Users2.mxitid = timeban.mxitid WHERE Users2.last_online < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY))"; mysql_query($sqldel) or die("Error: ".mysql_error()); //// end of removal/// //// removal of messages//// $query = "DELETE FROM StringyChat WHERE StringyChat_time < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY))"; mysql_query($query); printf("Messages deleted: %d\n", mysql_affected_rows()); //// removal of private messages//// $query1 = "DELETE FROM pm WHERE time < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY))"; mysql_query($query1); printf("Private Messages deleted: %d\n", mysql_affected_rows()); ?>

I've got two tables user table - id - username - fname - lname - group_id event Table - id - user_id - event_time - event_date - event ('Arrival','Departure','Break',etc..) My problem is getting all user by group and joining other table even date, but it should be base on 'event_date' I use `LEFT JOIN` and `LEFT OUTER JOIN` but because I have to specify the `event_date` not all user can be shown because some are absent. I made this SELECt * FROM user as u LEFT OUTER JOIN event as e ON u.id = e.user_id WHERE u.group_id = 6 AND ( e.event_date = '2016-07-05' AND e.event = 'Arrival' ) GROUP BY u.id but it only show the user who has an event on the date specified. I tried to make an VIEW table but it still has the condition base on event_date. Although I made this on PHP by condition but it takes time to load as I select event on every user. is there a way to get it using SQL? it should be like this: