-
Posts
24,613 -
Joined
-
Last visited
-
Days Won
834
Everything posted by Barand
-
nm!
-
Replace $buffer with $stream
-
// pow($x, 3) == 1200/3; // therefore $x = pow(1200/3 , 1/3); echo $x; //--> 7.3680629972808 (not 240, but close) to confirm echo 3 * 7.3680629972808 * 7.3680629972808 * 7.3680629972808; // --> 1200
-
Simple arithmetic // select the next perPage records $sql = "SELECT restaurant_name FROM restaurant ORDER BY restaurant_name LIMIT $offset, $perPage"; $res = $db->query($sql); $recs = $res->num_rows; $p = ($page - 1) * $perPage + 1; $q = $p + $recs - 1; $output = "Showing items $p to $q of $numrecs<br><br>";
-
Even if PHP is supported then changing the file names from .php to .html will prevent any php code from executing unless the server is reconfigured.
-
mysqli prepared statement mysqli_fetch_array()
Barand replied to aHMAD_SQaLli's topic in PHP Coding Help
Apologies for not being online when you posted. Had I known you were in such a hurry I would have sat in front of my screen all day just waiting for you to post a problem. -
Here's an example <?php session_start(); include("db_inc.php"); // defines HOST, USERNAME and PASSWORD try { $db = new mysqli(HOST,USERNAME,PASSWORD,'test'); } catch(Exception $e) { die("DB connection error"); } $perPage = 30; // set how many records to display on each page if (!isset($_SESSION['pages'])) { // count records to determine how many pages $sql = "SELECT COUNT(*) FROM restaurant"; $res = $db->query($sql); list($numrecs) = $res->fetch_row(); $_SESSION['pages'] = ceil($numrecs/$perPage); } $totalPages = $_SESSION['pages']; // get current page number $page = isset($_GET['page']) ? $_GET['page'] : 1; // calculate offset for query LIMIT clause $offset = ($page - 1) * $perPage; // select the next perPage records $sql = "SELECT restaurant_name FROM restaurant ORDER BY restaurant_name LIMIT $offset, $perPage"; $res = $db->query($sql); $output = ''; while ($row = $res->fetch_assoc()) { $output .= $row['restaurant_name'] . '<br>'; } function pageNav ($page, $total) { $str = 'Pages '; if ($total < 11) { for ($p=1; $p <= $total; $p++) { $class = $page==$p ? 'nolink' : 'link'; $str .= "<div class='nav $class'>$p</div>"; } } else { $class = $page==1 ? 'nolink' : 'link'; $str .= "<div class='nav $class'>1</div>"; if ($page < 5) { $p1 = 2; $p2 = 6; } else { $p1 = min ($page-2, $total-5); $p2 = min ($page+2, $total); $str .= '... '; } for ($p=$p1; $p <= $p2; $p++) { $class = $page==$p ? 'nolink' : 'link'; $str .= "<div class='nav $class'>$p</div>"; } if ($total-$page > 3) $str .= '... '; $class = $page==$total ? 'nolink' : 'link'; if ($total > $page+2) $str .= "<div class='nav $class'>$total</div>"; } return $str; } ?> <!DOCTYPE html> <html> <head> <title>Pagination Example</title> <script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script> <script type="text/javascript"> $().ready(function() { $(".link").click(function() { var pgnum = $(this).html(); location.href = "?page="+pgnum; // specify action on click of page number here }) }) </script> <style type="text/css"> div.nav { display: inline-block; width: 16px; height: 16px; margin-right: 5px; padding: 0; text-align: center; font-family: sans-serif; font-size: 9pt; } div.link { border: 1px solid blue; cursor: pointer; } div.nolink { font-weight: 700; border: none; } div#rests { font-family: sans-serif; font-size: 12pt; padding: 20px; color: #AA16B6; width: 400px; margin-top: 20px; margin-bottom: 20px; margin-left: auto; margin-right: auto; border-top: 1px solid gray; border-bottom: 1px solid gray; } div.pagenums { text-align: center; } </style> </head> <body> <h1>Restaurants</h1> <div class='pagenums'> <?=pageNav($page, $totalPages)?> </div> <div id='rests'> <?=$output?> </div> <div class='pagenums'> <?=pageNav($page, $totalPages)?> </div> </body> </html>
-
Find a User not in another table for specified dates
Barand replied to jbradley04's topic in MySQL Help
Joins are usually more efficient SELECT * FROM USER u LEFT JOIN stories s ON u.ID = s.CBy AND s.con BETWEEN '2014-07' AND '2015-07' WHERE s.CBy IS NULL BTW, you shouldn't have had the comma at the end of your first line -
How to select two tables and compare two columns and determine < or =
Barand replied to jgreen's topic in PHP Coding Help
try this SELECT b.id , b.title , b.copies FROM books b LEFT JOIN user_info u USING (title) GROUP BY b.id HAVING COUNT(u.title) < b.copies -
How to select two tables and compare two columns and determine < or =
Barand replied to jgreen's topic in PHP Coding Help
I see no title column in user_info table ??? -
Submit $_POST with dynamically generated fields
Barand replied to bambinou1980's topic in PHP Coding Help
I would go a step further than ch0cu3r and use the product's row id as the array indexes name="product[$id]" name="price[$id]" then you know which product the data relates to when processing the form -
Submit $_POST with dynamically generated fields
Barand replied to bambinou1980's topic in PHP Coding Help
You are listing all the products. Each one has dropdown menu with only a single option. What is the point of that? -
As ginerjm said, your link would be <a href = 'my_edit.php?id=9'>EDIT</a> In my_edit.php, query the database to get the current data using the id passed to it from the link SELECT comments FROM mytable WHERE id=$id Display a form with a <textarea name='comments'>$comments</textarea>, where $comments is from the database and put the id in a hidden input field. When the form is POSTed, update the database with the new comments for the same id
-
Here's a demo page of how I handle it. Sample output attached $page = isset($_GET['page']) ? $_GET['page'] : 1; $totalPages = 20; function pageNav ($page, $total) { $str = 'Pages '; if ($total < 11) { for ($p=1; $p <= $total; $p++) { $class = $page==$p ? 'nolink' : 'link'; $str .= "<div class='nav $class'>$p</div>"; } } else { $class = $page==1 ? 'nolink' : 'link'; $str .= "<div class='nav $class'>1</div>"; if ($page < 5) { $p1 = 2; $p2 = 6; } else { $p1 = min ($page-2, $total-5); $p2 = min ($page+2, $total); $str .= '... '; } for ($p=$p1; $p <= $p2; $p++) { $class = $page==$p ? 'nolink' : 'link'; $str .= "<div class='nav $class'>$p</div>"; } if ($total-$page > 3) $str .= '... '; $class = $page==$total ? 'nolink' : 'link'; if ($total > $page+2) $str .= "<div class='nav $class'>$total</div>"; } return $str; } ?> <html> <head> <title>Page Nav</title> <script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script> <script type="text/javascript"> $().ready(function() { $(".link").click(function() { var pgnum = $(this).html(); location.href = "?page="+pgnum; // specify action on click of page number here }) }) </script> <style type="text/css"> div.nav { display: inline-block; width: 16px; height: 16px; margin-right: 5px; padding: 0; text-align: center; font-family: sans-serif; font-size: 9pt; } div.link { border: 1px solid blue; } div.nolink { font-weight: 700; border: none; } </style> </head> <body> <h1>This is page <?=$page?></h1> <?=pageNav($page, $totalPages)?> </body> </html>
-
Congratulations. Mine's a gin'n'tonic
-
The first half of the UNION finds (for each trainer) free time before first booking free time between bookings all trainers time if no bookings The second part finds trainers' free time after last booking of the day. SELECT trainer , from_time , to_time , timeslot FROM ( SELECT a.day , TIMEDIFF(IFNULL(start_time,close_time), IF(a.trainer=@prevt,@prevend,open_time )) as timeslot , CAST(IF(a.trainer=@prevt,@prevend,open_time ) as TIME) as from_time , IFNULL(start_time,close_time) as to_time , @prevend := end_time as prevend , @prevt := a.trainer as trainer FROM bookingavailability a JOIN (SELECT @prevend:=null,@prevt:=null) as init LEFT JOIN bookingscalendar c ON a.trainer = c.trainer AND WEEKDAY(c.bookingdate) = a.day AND c.bookingdate = '2014-08-18' WHERE a.day = WEEKDAY('2014-08-18') ORDER BY a.trainer, c.start_time ) gaps UNION SELECT a.trainer , MAX(end_time) as from_time , a.close_time as to_time , TIMEDIFF(MAX(end_time),close_time) as timeslot FROM bookingavailability a INNER JOIN ( SELECT trainer , MAX(end_time) as end_time FROM bookingscalendar WHERE bookingdate = '2014-08-18' GROUP BY trainer ) eod WHERE a.day = WEEKDAY('2014-08-18') ORDER BY trainer,from_time;
- 30 replies
-
- php calendar
- mysql
-
(and 2 more)
Tagged with:
-
how i can i let user to download a data from query in mySQL
Barand replied to VanillaRose's topic in MySQL Help
This is the download function that I use. function sql2csv($mysqli, $sql, $filename='', $headings=1) /** * Parameters * $mysqli - connection * $sql - the sql query to be executed * $filename - name of download file (default "download_yymmddhhii.csv") * $headings - 1 if fieldname headings required (default), 0 if not required */ { if (!$filename) $f = 'download_' . date('ymdhi') . '.csv'; else $f = $filename; $fp = fopen('php://output', 'w'); // so you can fputcsv to STDOUT if ($fp) { $res = $mysqli->query($sql); if ($res) { header('Content-Type: text/csv'); header('Content-Disposition: attachment; filename="'.$f.'"'); header('Pragma: no-cache'); header('Expires: 0'); $row = $res->fetch_assoc(); if ($headings) { fputcsv($fp, array_keys($row)); } do { fputcsv($fp, $row); } while ($row = $res->fetch_assoc()); } else echo "Error in query"; fclose($fp); } } Use it in a php file that you link to with a download link. -
If you do that you won't know about the clients that are double-booked to inform them and change their bookings, you just hide the problem. This query will pull any booking where a room is booked at the same time as another booking. SELECT id ,bookingdate ,room ,start_time ,end_time ,trainer ,customer_id FROM bookingscalendar WHERE id IN ( SELECT b1.id FROM bookingscalendar b1 INNER JOIN bookingscalendar b2 ON b1.bookingdate = b2.bookingdate AND b1.room = b2.room AND b1.start_time < b2.end_time AND b1.end_time > b2.start_time AND b1.id <> b2.id ) ORDER BY bookingdate,room,start_time; Ensure at the time of booking that only free rooms can be allocated.
- 30 replies
-
- php calendar
- mysql
-
(and 2 more)
Tagged with:
-
Your availability is for trainer 2, your bookings for trainer 1. Dates will be null when no matching bookings for a trainer
- 30 replies
-
- php calendar
- mysql
-
(and 2 more)
Tagged with:
-
I notice you have added another level of complexity with the inclusion of the "room" in the bookings. So as well as checking for trainer availability you also have to check for room availability.
- 30 replies
-
- php calendar
- mysql
-
(and 2 more)
Tagged with:
-
These are my table definitions CREATE TABLE `bookingscalendar` ( `id` int(11) NOT NULL AUTO_INCREMENT, `bookingdate` date DEFAULT NULL, `trainer` int(11) DEFAULT NULL, `start_time` time DEFAULT NULL, `end_time` time DEFAULT NULL, `customer_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `bookingavailability` ( `availability_id` int(11) NOT NULL AUTO_INCREMENT, `trainer` int(11) DEFAULT NULL, `dayofweek` int(11) DEFAULT NULL, `open_time` time DEFAULT NULL, `close_time` time DEFAULT NULL, PRIMARY KEY (`availability_id`) )
- 30 replies
-
- php calendar
- mysql
-
(and 2 more)
Tagged with:
-
Booking date looks OK when I run it. I had to comment out the trainername column, that should be in a trainer table, not in availability table) mysql> SELECT trainer -> -- , trainername -> , dayofweek -> , bookingdate -> , CONCAT(from_time,'') as from_time -> , to_time, timeslot -> FROM -> ( -> SELECT a.trainer -> -- , a.trainername -> , dayofweek -> , bookingdate -> , TIMEDIFF(start_time, IF(bookingdate=@prevdate,@prevend,open_time )) as timeslot -> , IF(bookingdate=@prevdate,@prevend,open_time ) as from_time -> , start_time as to_time -> , @prevend := end_time as prevend -> , @prevdate := bookingdate as prevdate -> FROM bookingavailability a -> JOIN (SELECT @prevend:=null,@prevdate:=null) as init -> INNER JOIN bookingscalendar c -> ON a.trainer = c.trainer -> AND WEEKDAY(c.bookingdate) = a.dayofweek -> -> UNION -> -> SELECT a.trainer -> -- , a.trainername -> , dayofweek -> , bookingdate -> , TIMEDIFF(close_time, IFNULL(MAX(end_time),open_time) ) as timeslot -> , IFNULL(MAX(end_time),open_time) as from_time -> , close_time as to_time -> , null as prevend -> , null as prevdate -> FROM bookingavailability a -> LEFT JOIN bookingscalendar c -> ON a.trainer = c.trainer -> AND WEEKDAY(c.bookingdate) = a.dayofweek -> GROUP BY a.trainer,dayofweek,bookingdate -> ) as gaps -> WHERE timeslot > '00:00:00' -> ORDER BY trainer, dayofweek, bookingdate, from_time; +---------+-----------+-------------+-----------+----------+----------+ | trainer | dayofweek | bookingdate | from_time | to_time | timeslot | +---------+-----------+-------------+-----------+----------+----------+ | 1 | 0 | 2014-08-18 | 09:00:00 | 10:00:00 | 01:00:00 | | 1 | 0 | 2014-08-18 | 11:00:00 | 13:00:00 | 02:00:00 | | 1 | 0 | 2014-08-18 | 14:30:00 | 16:00:00 | 01:30:00 | | 1 | 0 | 2014-08-18 | 17:30:00 | 20:00:00 | 02:30:00 | | 1 | 1 | 2014-08-19 | 10:30:00 | 17:00:00 | 06:30:00 | | 1 | 2 | 2014-08-20 | 11:00:00 | 12:00:00 | 01:00:00 | | 1 | 2 | 2014-08-20 | 13:00:00 | 15:00:00 | 02:00:00 | | 1 | 3 | 2014-08-21 | 08:00:00 | 10:00:00 | 02:00:00 | | 1 | 3 | 2014-08-21 | 11:00:00 | 13:00:00 | 02:00:00 | +---------+-----------+-------------+-----------+----------+----------+ Check your data
- 30 replies
-
- php calendar
- mysql
-
(and 2 more)
Tagged with:
-
Calculating Inbreeding on a 10 Generation Pedigree
Barand replied to Triple_Deuce's topic in PHP Coding Help
Corrected version $db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE); $sql = "SELECT id, dogname, sire, dam FROM dogtable"; $dogs = $sires = $dams = array(); $res = $db->query($sql); while (list($id, $nm, $s, $d) = $res->fetch_row()) { $dogs[$id] = [$s,$d,$nm]; } function getAncestors($id, &$dogs, &$ancests, $dist) { if ($id==0) return; $ancests[$id] = $dist; if (isset($dogs[$id]) ) { getAncestors($dogs[$id][0], $dogs, $ancests, $dist+1); getAncestors($dogs[$id][1], $dogs, $ancests, $dist+1); } } $dogid = 1; getAncestors($dogs[$dogid][0], $dogs, $sires, 1); getAncestors($dogs[$dogid][1], $dogs, $dams, 1); ksort($sires); ksort($dams); $common = array_intersect_key($sires,$dams); echo "<pre>"; echo "| ID | NAME | SIRE | DAM |\n"; echo "| | | DIST | DIST |\n"; echo "|-----|--------------------|------|------|\n"; foreach ($common as $id => $dist) { printf("|%4d | %-18s | %4d | %4d |\n", $id, $dogs[$id][2], $sires[$id], $dams[$id]); } | ID | NAME | SIRE | DAM | | | | DIST | DIST | |-----|--------------------|------|------| | 8 | dog I | 3 | 2 | | 16 | dog Q | 4 | 3 | | 17 | dog R | 4 | 3 |- 40 replies
-
- inbreeding
- pedigree
-
(and 2 more)
Tagged with:
-
Calculating Inbreeding on a 10 Generation Pedigree
Barand replied to Triple_Deuce's topic in PHP Coding Help
PS - apologies for the bisexual dog in the data, but you get the idea- 40 replies
-
- inbreeding
- pedigree
-
(and 2 more)
Tagged with:
-
Calculating Inbreeding on a 10 Generation Pedigree
Barand replied to Triple_Deuce's topic in PHP Coding Help
A couple of years back I was working on dog pedigree charts so still had some test data. The chart of the data is attached and, as you can see, there are common ancestors. To find them you need to use a recursive function so I would load your data into an array and use that for the recursive search rather bombard your server with dozens of queries. This code will give the common ancestors and the generational distance on the sire and dam sides. You would then need to calc the IC value for each of these ancestors. $db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE); $sql = "SELECT id, dogname, sire, dam FROM dogtable"; $dogs = $sires = $dams = array(); $res = $db->query($sql); while (list($id, $nm, $s, $d) = $res->fetch_row()) { $dogs[$id] = [$s,$d,$nm]; } function getAncestors($id, $key, &$dogs, &$ancests, $dist) { if ($id==0) return; $ancests[$id] = $dist; if (isset($dogs[$id]) ) { getAncestors($dogs[$id][$key], 0, $dogs, $ancests, $dist+1); getAncestors($dogs[$id][$key], 1, $dogs, $ancests, $dist+1); } } $dogid = 1; getAncestors($dogs[$dogid][0], 0, $dogs, $sires, 0); getAncestors($dogs[$dogid][1], 1, $dogs, $dams, 0); ksort($sires); ksort($dams); $common = array_intersect_key($sires,$dams); echo "<pre>"; echo "| ID | NAME | SIRE | DAM |\n"; echo "| | | DIST | DIST |\n"; echo "|-----|--------------------|------|------|\n"; foreach ($common as $id => $dist) { printf("|%4d | %-18s | %4d | %4d |\n", $id, $dogs[$id][2], $sires[$id], $dams[$id]); } Outputs | ID | NAME | SIRE | DAM | | | | DIST | DIST | |-----|--------------------|------|------| | 8 | dog I | 2 | 1 | | 16 | dog Q | 3 | 2 | | 17 | dog R | 3 | 2 |- 40 replies
-
- 1
-
-
- inbreeding
- pedigree
-
(and 2 more)
Tagged with: