Jump to content

Search the Community

Showing results for tags 'sql'.

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


Forums

  • Welcome to PHP Freaks
    • Announcements
    • Introductions
  • PHP Coding
    • PHP Coding Help
    • Regex Help
    • Third Party Scripts
    • FAQ/Code Snippet Repository
  • SQL / Database
    • MySQL Help
    • PostgreSQL
    • Microsoft SQL - MSSQL
    • Other RDBMS and SQL dialects
  • Client Side
    • HTML Help
    • CSS Help
    • Javascript Help
    • Other
  • Applications and Frameworks
    • Applications
    • Frameworks
    • Other Libraries
  • Web Server Administration
    • PHP Installation and Configuration
    • Linux
    • Apache HTTP Server
    • Microsoft IIS
    • Other Web Server Software
  • Other
    • Application Design
    • Other Programming Languages
    • Editor Help (PhpStorm, VS Code, etc)
    • Website Critique
    • Beta Test Your Stuff!
  • Freelance, Contracts, Employment, etc.
    • Services Offered
    • Job Offerings
  • General Discussion
    • PHPFreaks.com Website Feedback
    • Miscellaneous

Find results in...

Find results that contain...


Date Created

  • Start

    End


Last Updated

  • Start

    End


Filter by number of...

Joined

  • Start

    End


Group


AIM


MSN


Website URL


ICQ


Yahoo


Jabber


Skype


Location


Interests


Age


Donation Link

  1. 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...
  2. 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
  3. 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'; ?>
  4. 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;
  5. 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?
  6. 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!
  7. <?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"); } } ?>
  8. 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:
  9. 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?
  10. 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()); ?>
  11. I am reading an RSS feed and sometimes I pull in duplicates of data. $citable= "INSERT INTO TableName (Company, Form, Date, Link) VALUES ('" . $FnumI ."',' " . $Form ."','" . $msqldate ."','" . $Flink ."') ON DUPLICATE KEY UPDATE Company='" . $FnumI ."', Form='" . $Form ."', Date='" . $msqldate ."', Link='" . $Flink ."'"; Is there a way I can only record the data if Link does not exist?
  12. I am trying to delete all the rows that have a duplicate URL in my table. I tried this code: but it is giving me an error: You can't specify target table 'TableName' for update in FROM clause DELETE FROM TableName WHERE ID NOT IN (SELECT MAX(ID) FROM TableName GROUP BY Link HAVING MAX(ID) IS NOT NULL)
  13. I want to fetch the titles from the Courses table, but I only want the Course Title to return once when I join Events. Courses CID | CourseTitle 1 | Course A 2 | Course B 3 | Course C Events EID | CID | EventDate 1 | 1 | 2016-02-22 2 | 1 | 2016-02-23 3 | 2 | 2016-02-24 4 | 3 | 2016-02-25 5 | 3 | 2016-02-26 If I use a JOIN, SELECT Courses.CourseTitle FROM Courses LEFT JOIN Events on (Events.CID = Courses.CID) then I get Course A Course A Course B Course C Course C But what I want is Course A Course B Course C Because ultimately, I'm going to select an Event date range, and I want to see just the courses with the event date range. Thanks!
  14. Hi all, I'm running an update sql query from my wordpress functions file when a form is submitted. This grabs the address fields, uses the Google API and gets the lat and lng co-ords so I can utilise these elsehwere. My issue is the warning I get when I submit the form. My codes below: $meta_value = array( 'address' => $full_address, 'lat' => $lat, 'lng' => $lng ); #$location = array($full_address,$lat,$lng); print_r($meta_value); $wpdb->show_errors(); #$wpdb->update( $table, $data, $where ); $wpdb->update( $wpdb->postmeta, array( 'meta_value' => $meta_value ), array( "meta_key" => 'location', "post_id" => $post_id )); $wpdb->print_error(); var_dump( $wpdb->last_query ); However, when it runs, I get the warning: Warning: mysql_real_escape_string() expects parameter 1 to be string, It should pass in the values from the Array: Array ( [address] => Address 1, Town, County AB12 3CD, United kingdom [lat] => 50.123456 [lng] => 0.1234567 ) I can't see for looking now so any help is much appreciated!
  15. I am trying to set a column equal to the column in another table where the date matches. I seem to have a problem in my query and I have tried multiple methods. Can someone tell me where the mistake is? $query2 = "UPDATE a SET a.CalDatePrice = b.Close FROM ". $symbol ." b INNER JOIN CleanedCalendar a ON a.BuyDate = b.Date"; 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 'FROM CLBS b INNER JOIN CleanedCalendar a ON a.BuyDate = b.Dat' at line 3
  16. Hi I have 2 tables I need to update according to the value of the 3rd table field currently I do 2 queries but I'm sure there must be a way to make it 1 query query 1 $sql = "UPDATE Room_users INNER JOIN Users2 u ON u.mxitid = Room_users.mxitid SET Room_users.User = u.Username"; query 2 $sql = "UPDATE Rooms INNER JOIN Users2 u ON u.mxitid = Rooms.mxitid SET Rooms.creator = u.Username"; How can I turn this into 1 query?
  17. I have stored procedure in database: DELIMITER $$ USE `billing`$$ DROP PROCEDURE IF EXISTS `Pivot`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `Pivot`( IN tbl_name VARCHAR(99), -- table name (or db.tbl) IN base_cols VARCHAR(99), -- column(s) on the left, separated by commas IN pivot_col VARCHAR(64), -- name of column to put across the top IN tally_col VARCHAR(64), -- name of column to SUM up IN where_clause VARCHAR(99), -- empty string or "WHERE ..." IN order_by VARCHAR(99) -- empty string or "ORDER BY ..."; usually the base_cols ) DETERMINISTIC SQL SECURITY INVOKER BEGIN -- Find the distinct values -- Build the SUM()s SET @subq = CONCAT('SELECT DISTINCT ', pivot_col, ' AS val ', ' FROM ', tbl_name, ' ', where_clause, ' ORDER BY 1'); -- select @subq; SET @cc1 = "CONCAT('SUM(IF(&p = ', &v, ', &t, 0)) AS ', &v)"; SET @cc2 = REPLACE(@cc1, '&p', pivot_col); SET @cc3 = REPLACE(@cc2, '&t', tally_col); -- select @cc2, @cc3; SET @qval = CONCAT("'\"', val, '\"'"); -- select @qval; SET @cc4 = REPLACE(@cc3, '&v', @qval); -- select @cc4; SET SESSION group_concat_max_len = 10000; -- just in case SET @stmt = CONCAT( 'SELECT GROUP_CONCAT(', @cc4, ' SEPARATOR ",\n") INTO @sums', ' FROM ( ', @subq, ' ) AS top'); SELECT @stmt; PREPARE _sql FROM @stmt; EXECUTE _sql; -- Intermediate step: build SQL for columns DEALLOCATE PREPARE _sql; -- Construct the query and perform it SET @stmt2 = CONCAT( 'SELECT ', base_cols, ',\n', @sums, ',\n SUM(', tally_col, ') AS Total' '\n FROM ', tbl_name, ' ', where_clause, ' GROUP BY ', base_cols, '\n WITH ROLLUP', '\n', order_by ); SELECT @stmt2; -- The statement that generates the result PREPARE _sql FROM @stmt2; EXECUTE _sql; -- The resulting pivot table ouput DEALLOCATE PREPARE _sql; -- For debugging / tweaking, SELECT the various @variables after CALLing. END$$ DELIMITER ; When i pass a SQL statement i want to capture the final result in rows with column headers and pass it to the HTML Table output on PHP. CALL Pivot('production', 'product_name', 'market', 'forcast_qty', \"WHERE production_date = '2015-12-31'\", ''); So far when i code the following procedure to get the output i am getting a array but not the end result of the stored procedure. if(isset($_POST['planned_forecast'])) { if(isset($_POST['production_date'])){ $date_prod = $_POST['production_date']; } $stmt = $DB_con->prepare("CALL Pivot('production', 'product_name', 'market', 'forcast_qty', \"WHERE production_date = '2015-12-31'\", '');"); $stmt->execute(); while ($row = $stmt->fetchAll(PDO::FETCH_ASSOC)) { print_r($row); } echo ' <thead>'; echo ' </thead> '; echo ' <tbody> '; echo ' <tr> '; echo ' <td></td> '; echo ' </tr> '; echo ' </tbody> '; echo '</table>'; echo '<input hidden="true" name="date_prod" type="text" value="'.$date_prod.'"/>'; echo '<button type="submit" class="btn btn-default waves-effect waves-light" name="btn-editforcast" id="btn-editforcast">Update</button>'; } Result: Array ( [0] => Array ( [@stmt] => SELECT GROUP_CONCAT(CONCAT('SUM(IF(market = ', '"', val, '"', ', forcast_qty, 0)) AS ', '"', val, '"') SEPARATOR ", ") INTO @sums FROM ( SELECT DISTINCT market AS val FROM production WHERE production_date = '2015-12-31' ORDER BY 1 ) AS top ) )
  18. Hello I want to make a SQL row to be deleted after one day, or week, or every row older than on week automatically, how can it be done ? Have a good day.
  19. is it possible to insert and delete from the same table using the same transaction in mysql. something like: START TRANSACTION; DELETE FROM Al where vc='frdehy' ; INSERT INTO Al(vc,zzz ,subj_id) VALUES('bgo' ,'mij','rey'); commit;
  20. I have three tables in my database listed below: Table: teams_info team_id team_name entry_year status 1 team 1 2015 1 2 team 2 2015 1 3 team 2 2016 1 Table: team_players player_id team_id status members_id position 1 1 0 1 1 2 1 1 2 2 3 1 1 3 3 4 1 1 4 4 5 2 0 5 1 6 2 0 6 2 7 2 1 7 3 Table: members members_id first_name surname gender 1 joe blogg male 2 james smith male 3 sarah marshall female 4 tony walker male 5 peter jones male 6 jessica varley female 7 jane varley female I'm trying to get my head around how I would get the following information, 1) How many team_players from team_info.entry_year = 2015 have accepted the to join a team (this will be all players with status = 1) 2) How many team_players from team_info.entry_year = 2015 have been invited (basically counting all records that belong to 2015 teams) 3) The total Males players from 2015 teams. 4) The total female players from 2015 teams. i'm fairly new to SQL and it seems I have made things complicated whilst trying to follow the best standards, however I can not wrap my head around where to start. Any help would be greatly appreciated.
  21. Is this allowed in mysql transactions;Can i insert conditionally into different tables by different users to insert data : i have tried this inside a stored procedure,but can someone correct me if it is good approach: START TRANSACTION; INSERT INTO minderstbl(m_date,m_notes )VALUES(in_date,in_remindernotes); SELECT Rm_id INTO l_rem_id FROM minderstbl where m_date=in_date and m_notes=in_remindernotes limit 1; IF in_status = 1 THEN INSERT INTO i_ndertbl(id,Doc_id,frq_no,eq_text )VALUES(l_rem_id,in_doctitleid,in_freq_no,in_freq_text); ELSEIF in_status = 2 THEN INSERT INTO nderstbl(ct_id,em_id,eq_no,q_text )VALUES(in_compid,l_rem_id ,in_freq_no,in_freq_text); ELSEIF in_status = 3 THEN INSERT INTO rstbl(m_id,q_no,q_text )VALUES(l_rem_id ,in_freq_no,in_freq_text); END IF; if l_rem_id !='' then SELECT l_rem_id ; end if; COMMIT;
  22. Hello, This is my first attempt at sql sub queries, so please excuse me if it's something very silly. Below is my sql: SELECT td.*, CONCAT(me.First_Name,' ', me.Surname) AS managers_full_name, me.Telephone AS managers_telephone, td.status AS status FROM `team_details` td JOIN `members` me ON me.id = td.managers_user_id JOIN (SELECT COUNT(*) AS nap FROM `team_players`) AS tp ON td.team_id = tp.team_id WHERE tp.status = '1' WHERE td.entry_year = '2016' The SQL executed fine before I added the second join with a sub query, now I'm getting the following error: #1064 - 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 'WHERE td.entry_year = '2016' LIMIT 0, 25' at line 5 Is this not the correct way of sub querying, or have I missed something? Any help would be greatly appreciated.
  23. Hi everyone, I don't understand why MySQL won't do execute query to delete record in phpmyadmin. I made a script in PHP, what did I do wrong? First page: <?php session_start(); if ($_SESSION['user']) { echo "<p>You are logging in as ".$_SESSION['user']." <a href='logout.php'>Log out</a></p>"; } else { header('location:denied.php'); } require('require.php'); ?> <!doctype html> <html> <head> <title>Delete your member's information</title> <link href="rcd.css" rel="stylesheet" type="text/css"> <link href="submit.css" rel="stylesheet" type="text/css"> </head> <body> <center> <p>Are you sure you want to delete this member's information?</p> <form action="delete2.php"> <table> <?php $del = $_GET['delete']; $show = "SELECT * FROM Members WHERE ID = '".$del."'"; $result = mysqli_query($Garydb, $show); if (mysqli_num_rows($result)) { while ($rows = mysqli_fetch_assoc($result)) { echo "<tr><td>First Name: </td><td><input type='text' value='".$rows['FirstName']."'></td></tr>"; echo "<tr><td>Last Name: </td><td><input type='text' value='".$rows['LastName']."'></td></tr>"; echo "<tr><td>Birth Month: </td><td><input type='text' value='".$rows['Month']."'></td></tr>"; echo "<tr><td>Email: </td><td><input type='text' value='".$rows['Email']."'></td></tr>"; echo "<tr><td>Local: </td><td><input type='text' value='".$rows['Local']."'></td></tr>"; echo "<tr><td colspan='2'><a href='delete2.php?delete2=".$rows['ID']."' style='font-size:20px;'>Delete Member's Information</a></td></tr>"; } } ?> </table> </form> <p><a href="register.php">Return to register page</a></p> </center> </body> </html> Second Page <?php session_start(); if ($_SESSION['user']) { echo $_SESSION['user']; }else { header('denied.php'); } require("require.php"); if ($_GET['delete2']) { $delete = $_GET['delete2']; } if ($delete) { $del = "DELETE * FROM Members WHERE ID = '".$delete."'"; mysqli_query($Garydb, $del); } ?> <!doctype html> <html> <head> <title>Deleted</title> </head> </html>
  24. Is there an easy way to change dates in a column so that if the date falls on a Saturday or Sunday it changes to the closest business day?
  25. I have a table of prices by date and I am trying to select two rows, the 2nd row and a fixed row. It the below statement I am trying to get the date less than the current date but it is returning mixed results. How can I get just the row for .$Hist['BuyDateSubmit']. and the row with the date which is on the 2nd row from the top? $HistQ = "select * from TABLE where Date = '".$Hist['BuyDateSubmit']. "' AND (Date <'". $XXX . "' OR Date = '" . $Hist['BuyDateSubmit'] ."')";
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.