Barand
-
Posts
24,515 -
Joined
-
Last visited
-
Days Won
819
Community Answers
-
Barand's post in updating table query was marked as the answer
try
UPDATE ballot INNER JOIN ( SELECT username , COUNT(neptune) as ncount FROM ballot GROUP BY username HAVING ncount > 5 ) n USING (username) SET morethan5 = 'yes' However, it is bad practice to store derived data like that in a db table.
-
Barand's post in PHP Questionnaires Setup and structure was marked as the answer
FooKelvin,
I have attached a data model to get you started. You should employ this same approach to your problem your other topic at
http://forums.phpfreaks.com/topic/300461-generate-report-from-check-box-value-comments/?do=findComment&comment=1529890
-
Barand's post in formatting php count results was marked as the answer
There is definitely something going on. If I create a test table your code is fine
mysql> SELECT id, username FROM ballot; +----+--------------+ | id | username | +----+--------------+ | 1 | habsfan4life | | 2 | demo | | 3 | habs4stanley | | 4 | habsfan4life | | 5 | demo | +----+--------------+ 5 rows in set (0.00 sec) mysql> SELECT username, COUNT(*) -> FROM ballot -> GROUP BY username; +--------------+----------+ | username | COUNT(*) | +--------------+----------+ | demo | 2 | | habs4stanley | 1 | | habsfan4life | 2 | +--------------+----------+ 3 rows in set (0.00 sec) -
Barand's post in Beginner needs help.... was marked as the answer
Select the data you want to output with a WHERE clause
SELECT dt, people, c_fname, c_lname, c_phone, c_notes, code FROM restaurantbooking_bookings WHERE dt BETWEEN CURDATE() AND CURDATE() + INTERVAL 3 DAY ORDER BY dt -
Barand's post in Get PHP Variables with Ajax or Jquery was marked as the answer
Easiest way to get the form fields is to use serialize(). Try this example
testing.html
<html> <head> <title>Example</title> <script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script> <script type='text/javascript'> $().ready(function() { $('#BtnInserisci').click(function() { var formdata = $("#form1").serialize(); $.post( "testing.php", formdata, function(html) { $('#BtnInserisci').before(html); }, 'html' ) }) }) </script> </head> <body> <form id='form1'> Field 1: <input type='text' name='field1' value='aaaa'><br> Field 2: <input type='text' name='field2' value='bbbb'><br> Field 3: <input type='text' name='field3' value='cccc'><br> <input type='button' name='BtnInserisci' id='BtnInserisci' value='Test'> </form> </body> </html> testing.php
<?php $html = ''; if ($_SERVER['REQUEST_METHOD']=='POST') { $html = '<div style="width: 200px; border: 1px solid gray; margin:10px;padding:5px">'; foreach ($_POST as $k => $v) { $html .= "<p>$k : $v</p>"; } $html .= "</div>\n"; } echo $html; ?> -
Barand's post in WEEKLY QUERY was marked as the answer
I think it's fixed. Try
SELECT s.week , CONCAT(DATE_FORMAT(@day1 + INTERVAL (s.week-1)*7 DAY, '%e %b'),' - ', DATE_FORMAT(@day1 + INTERVAL (s.week-1)*7 + 6 DAY, '%e %b')) as wkcomm , @slab:=@slab+ifnull(slabcount,0)-@driedcf as slab_count , @dried:=@dried+ifnull(driedcount,0)-@tiledcf as dried_count , @driedcf:=ifnull(driedcount,0) as x1 , @tiledcf:=ifnull(tiledcount,0) as x2 FROM ( SELECT @wk := 0 as init0 , @day1 := '2015-01-04' as init1 , @slab := (SELECT count(lot_id) FROM lot INNER JOIN block b USING (block_id) INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3) WHERE slab_date < @day1 AND IFNULL(dried_in_date,'9999-12-31')>=@day1) as init2 , @dried := (SELECT COUNT(lot.lot_id) FROM lot INNER JOIN block b USING (block_id) INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3) LEFT JOIN schedule s ON lot.lot_id=s.lot_id AND work_type_id=1 AND complete_date < @day1 WHERE dried_in_date < @day1 AND s.lot_id IS NULL) as init3 , @driedcf := 0 as init4 , @tiledcf := 0 as init5 ) init INNER JOIN ( SELECT COUNT(lot_id) as slabcount , DATE_FORMAT(slab_date, '%V') as week FROM lot INNER JOIN block b USING (block_id) INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3) WHERE DATE_FORMAT(slab_date, '%X') = DATE_FORMAT(@day1, '%X') GROUP BY week ) s LEFT JOIN ( SELECT COUNT(lot_id) as driedcount , DATE_FORMAT(dried_in_date, '%V') as week FROM lot INNER JOIN block b USING (block_id) INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3) WHERE DATE_FORMAT(dried_in_date, '%X') = DATE_FORMAT(@day1, '%X') AND dried_in_date is not null GROUP BY week ) d using (week) LEFT JOIN ( SELECT COUNT(s.lot_id) as tiledcount , DATE_FORMAT(complete_date, '%V') as week FROM lot INNER JOIN block b USING (block_id) INNER JOIN community c on b.community_id = c.community_id AND c.contract_type_id IN (1,3) INNER JOIN schedule s ON lot.lot_id=s.lot_id AND work_type_id=1 AND complete_date IS NOT NULL WHERE DATE_FORMAT(complete_date, '%X') = DATE_FORMAT(@day1, '%X') GROUP BY week ) t using (week) ; -
Barand's post in Range with Letters and Numbers was marked as the answer
SELECT whatever FROM mytable WHERE mycol BETWEEN '1A' AND '5D' -
Barand's post in Best way to design these tables and query them? was marked as the answer
Yes.
SELECT member_name , studs.member_id , GROUP_CONCAT(school_name, '(', s.school_id, ')' SEPARATOR ', ') as schools FROM ( SELECT member_name , member_id FROM members WHERE created_by = 10 AND role_id = 2 UNION -- -- select students from schools -- created by admin #10 -- SELECT member_name , m.member_id FROM members m INNER JOIN school_members sm USING (member_id) INNER JOIN schools s USING (school_id) WHERE s.created_by=10 AND m.role_id = 2 UNION -- -- select students from schools -- where admin #10 is a member -- SELECT member_name , m.member_id FROM members m INNER JOIN school_members sm1 ON m.member_id = sm1.member_id INNER JOIN school_members sm2 ON sm1.school_id = sm1.school_id WHERE sm2.member_id = 10 AND m.role_id = 2 ) studs LEFT JOIN school_members USING (member_id) LEFT JOIN schools s USING (school_id) GROUP BY member_name ; -
Barand's post in Query help - looking for members of two different groups was marked as the answer
In that case you need to connect twice to the user_role table
SELECT users.uid, users.`name`, users.mail, users.created, ur1.rid as rid1, ur2.rid as rid2, field_data_field_first_name.field_first_name_value, field_data_node_venue_region.node_venue_region_tid FROM users INNER JOIN users_roles ur1 ON users.uid = ur1.uid AND ur1.rid = 7 INNER JOIN users_roles ur2 ON users.uid = ur2.uid AND ur2.rid = 8 INNER JOIN field_data_field_first_name ON field_data_field_first_name.entity_id = users.uid INNER JOIN field_data_node_venue_region ON field_data_node_venue_region.entity_id = users.uid -
Barand's post in Creating relationship tables from JSON data.... was marked as the answer
This should show you the logic you need to employee to process your data. (Note this is pseudocode, not runnable code)
$jsondata = '[{"payee":"John Doe","amount":5.25},{"payee":"Joe Smith","children":[{"amount":10.25,"category":"Groceries"},{"amount":5.75,"category":"Dining out"}]}]'; $data = json_decode($jsondata,1); foreach ($data as $trans) { if (isset($trans['children'])) { insert into transaction (payee) VALUES '{$trans[payee]}' $parentid = last_insert_id(); foreach ($trans['children'] as $child) { insert into transaction (parent,amount,category) VALUES ($parentid, $child[amount], $child[category]) } } else { insert into transactions (payee,amount) VALUES ($trans[payee], trans[amount]) } } -
Barand's post in I need some help. Trying to show label before showing pictures in a row. was marked as the answer
If you are looping through the items when outputting the results, why are you using $row['cid'] etc instead of $item['cid']?
-
-
Barand's post in Column 'description' cannot be part of FULLTEXT index was marked as the answer
You need MySQL 5.6+ for fulltext on InnoDB
-
Barand's post in Trouble with LOAD DATA INFILE was marked as the answer
I would think the "\r\n" is the cure. Let us know.
-
Barand's post in PDO delete mysql records from multiple tables where the id is the same was marked as the answer
I see, different in each table. Need to change the syntax
DELETE table1, table2, table3 FROM table1 LEFT JOIN table2 ON Rooms.id = Room_users.Roomid LEFT JOIN table3 ON Rooms.id = Room_chats.roomid WHERE table1.time < UNIX_TIMESTAMP() -
Barand's post in Very strange behavior was marked as the answer
Your second code doesn't "return" the rows it just prints the array containing the rows.
Is that code inside a function? If not you shouldn't be using return. If it is, how are you processing the results returned by the first code.
You should not be running queries inside a loop, you should use a JOIN to get all the results in a single query call, like this
SELECT p.id , p.title , p.description FROM posts as p JOIN post_members as pm ON p.id = pm.post_id WHERE pm.userid = :userid -
Barand's post in delete a SQL row after a week was marked as the answer
You would use Windows Scheduler or a cron job (linux) to invoke the query.
-
Barand's post in Left join and SUM problem was marked as the answer
I suspect that some of your rows are duplicated because of the joins
-
Barand's post in php login issue was marked as the answer
session_start() needs be called before any output is sent to the browser. You are currently sending the form fields first. Move the php code above the form code.
You are attempting to use the content of $_POST['mail'] and $_POST['pass'] before you check if any data was POSTed.
You only retrieve a single record with the query, so why is there a while() loop?
When you use header() to redirect, there should be an exit; command following it to prevent the rest of the script from being executed.
Use password_hash() and password_verify()
-
Barand's post in Retrieving Birthday from database MySQL php problem was marked as the answer
That will only get those who were born today, not whose birthday falls today.
SELECT username , pdob , YEAR(CURDATE())-YEAR(pdob) as age FROM table WHERE MONTH(pdob)=MONTH(CURDATE()) AND DAY(pdob)=DAY(CURDATE()) -
Barand's post in Insert multiple rows in mysqli prepared statement was marked as the answer
then
$beneficiary = 123; // or whatever $bank_data = array ( array($branchId1 , $acc1), array($branchId2 , $acc2) ); $sql = "INSERT INTO user_bank (beneficiary_id, branch_id, account VALUES (?,?,?))"; $stmt = $db->prepare($sql); $stmt->bind_param('ii', $benficiary,$branch, $acc); foreach ($bank_data as $bdata) { list($branch, $acc) = $bdata; $stmt->execute(); } -
Barand's post in Finding a route in mysql was marked as the answer
Having seen your data reminded me you need to join on SectorDate too.
Mk III version
SELECT * FROM ( SELECT Dep as depA , BeginTime as deptimeA , Arr as arrA , EndTime as arrtimeA , SectorDate FROM rosters WHERE Dep='MAN' AND BeginTime > '03:00' AND SectorDate = '2015-11-26' ) a LEFT JOIN ( SELECT Dep as depB , BeginTime as deptimeB , Arr as arrB , EndTime as arrtimeB , SectorDate FROM rosters ) b ON arrA = depB AND arrtimeA < deptimeB AND a.SectorDate = b.SectorDate LEFT JOIN ( SELECT Dep as depC , BeginTime as deptimeC , Arr as arrC , EndTime as arrtimeC , SectorDate FROM rosters ) c ON arrB = depC AND arrtimeB < deptimeC AND b.SectorDate=c.SectorDate WHERE 'DUB' IN (arrA, ArrB, ArrC) -
Barand's post in Error when using mysql sub query was marked as the answer
If you want the count for each team
JOIN ( SELECT team_id, status, COUNT(*) AS nap FROM `team_players` WHERE status = '1' GROUP BY team_id ) AS tp ON td.team_id = tp.team_id -
Barand's post in how to create timezone select menu using PHP was marked as the answer
perhaps
$arr = timezone_identifiers_list(); foreach ($arr as $tz) { if (strpos($tz, '/')===false ) continue; list ($region, $city) = explode('/', $tz, 2); $d = new DateTimeZone($tz); $secs = $d->getOffset(new DateTime()); $offset = number_format($secs/3600, 2); $tzArray[$offset][$region][] = $city; } ksort($tzArray); $menu = "<option value=''>- select timezone -</option>\n"; foreach ($tzArray as $o => $regzones) { $menu .= sprintf("<optgroup label='GMT %+0.2f'>\n", $o); ksort($regzones); foreach ($regzones as $region => $cities) { foreach ($cities as $city) $menu .= "<option >$region/$city</option>\n"; } $menu .= "</optgroup>\n"; } ?> <select name='tzone'> <?=$menu?> </select> -
Barand's post in swap column values was marked as the answer
Look again!
before: ID: 14 Cover:1 after: ID: 14 Cover:0 Both the image and cover are changed.
If your objective is to switch which is the main image, just change cover and not the image name (or vice versa)