Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,563

  • Joined

  • Last visited

  • Days Won

    822

 Content Type 

Everything posted by Barand

  1. Barand
    while (list($a, $b, $c) = $result->fetch_row()) { $data[$a][$b][] = $c; }
  2. Barand
    Perhaps this in euser.php $sql = "SELECT id, username FROM $tbl_name ORDER BY username"; $result = $con->query($sql); echo "<table cellpadding='5'>"; while ($row = $result->fetch_assoc()) { echo "<tr><td>{$row['username']}</td> <td><a href='editUser.php?id={$row['id']}'>Edit User</a></td> <td><a href='changepassword.php?id={$row['id']}'>Change Password</a></td> <td><a href='banUser.php?id={$row['id']}'>Ban User</a></td> </tr>\n"; } echo "</table>\n";
  3. Barand
    There is if you design your tables correctly and don't just throw different bits of data together into a single field. As a workaround for now you could try ... GROUP BY SUBSTRING_INDEX(clid, '+', 1)
  4. Barand
    Define you styles once in the <head>..</head> section of your page, not every time you output something to the page. eg <html> <head> <style> a { color: blue; text-decoration: none; } a:hover { color: #ff0000; } body { background-color: #000; } </style> </head> or put the styles in an external .css file. Having linked to "edituser.php?id=1" passing the id of the selected user, why would you then run a query to get the ids of all the users again? Get the details of the selected user for editing (ie. change password, set a flag to ban them etc.)
  5. Barand
    Total count for each client SELECT clid, COUNT(*) as tot FROM tablename GROUP BY clid Total count each day for each client SELECT clid, date, COUNT(*) as tot FROM tablename GROUP BY clid, date
  6. Barand
    IF they are consistent, yes. I'd advise running SELECT DISTINCT category FROM book WHERE ABS(category) = 0 ORDER BY category and checking the list before going ahead
  7. Barand
    Bite the bullet and clean up your data. Create categories in the category table for those that are not already there (such as Classic Kids) and store category id in the book table. I know this this may be as easy as it sounds. I once did a similar data migration exercise where a company department had been storing supplier invoice details in a home-built Access database. One of the data fields was the company name. Some companies had hundreds of invoices stored and it was rare to find every record for a company having exactly the same spelling of the name. Some had half a dozen variations of the same name. And that is a reason NOT to store names but to use the ids.
  8. Barand
    As you want to output income and expenditure totals then those might be good totals to store, don't you think? Something like this $mysqli = new mysqli(HOST,USERNAME,PASSWORD,'test'); $inSql = "SELECT , YEAR(si.date_invoiced) as year , MONTH(si.date_invoiced) AS month , SUM(li.sub_total) AS total , SUM(li.tax_amount) AS total_tax FROM sales_invoice si INNER JOIN sales_invoice_line_items li ON si.invoice_id = li.invoice_id GROUP BY year, month"; $exSql = "SELECT , YEAR(pi.date_invoiced) AS year , MONTH(pi.date_invoiced) AS month , SUM(li.sub_total) AS total , SUM(li.tax_amount) AS total_tax FROM purchase_invoice pi INNER JOIN purchase_invoice_line_items li ON pi.invoice_id = li.invoice_id GROUP BY year, month"; $data = array(); // GET INCOME TOTALS $res = $db->query($inSql); while (list($y, $m, $tot, $tax) = $res->fetch_row()) { if ($m < 4) $y--; // jan, feb, mar belong in previous year $m = ($m + % 12; // adjust month number $data[$m][$y]['i'] += $tot - $tax; } // GET EXPEND TOTALS $res = $db->query($exSql); while (list($y, $m, $tot, $tax) = $res->fetch_row()) { if ($m < 4) $y--; // jan, feb, mar belong in previous year $m = ($m + % 12; // adjust month number $data[$m][$y]['x'] += $tot - $tax; } // LOOP THROUGH DATA ARRAY AND OUTPUT TOTALS $mnames = array('Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'); $totals = array(); foreach ($data as $m => $mdata) { echo "<tr><td>{$mnames[$m]}</td>"; foreach ($mdata as $y => $ydata) { echo "<td>{$ydata[i]}</td><td>{$ydata[x]}</td>"; $totals[$y]['i'] += $ydata['i']; $totals[$y]['x'] += $ydata['x']; } echo "</tr>\n"; } // echo total row here from $totals array
  9. Barand
    What are you trying to do? If you are trying to change the appearance of alternate rows then a. The value of $nr_row needs to change b. It needs to change within your loop
  10. Barand
    Look up the category_id in your category table if a name is entered instead of an id. SELECT category_id FROM category WHERE cat_name = '$googleCategory'
  11. Barand
    http://phpsecurity.org/code/ch02-5
  12. Barand
    Don't double post ! This has already been answered in the MySQL forum.
  13. Barand
    A few line breaks would have helped (use print_r() between <pre>..</pre> tags ) or use var_export() which produces useable code. It looks like you want $m->response->amount;
  14. Barand
    Plan B $sql = "SELECT player_id FROM players"; $res = $mysqli->query($sql); $players = array(); while ($row = $res->fetch_row() ) { $players[] = $row[0]; } shuffle($players); $teams = array_chunk($players, 5); foreach ($teams as $k => $team) { $tm = $k+1; $members = join(',', $team); $mysqli->query("UPDATE players SET team = $tm WHERE player_id IN ($members)"); } If you needed a single-query update because of volume you could generate a query like this using the above chunks UPDATE players SET team = CASE WHEN player_id IN (11,2,4,5,7) THEN 1 CASE WHEN player_id IN (10,9,8,6,1) THEN 2 CASE WHEN player_id IN (3) THEN 3 END;
  15. Barand
    Oops! You're right. I was trying to avoid running queries in loops. if the table is this: CREATE TABLE `players` ( `player_id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `team` int(11) DEFAULT NULL, PRIMARY KEY (`player_id`) ) then when running your first query mysql> SELECT * FROM players; +-----------+------+------+ | player_id | name | team | +-----------+------+------+ | 1 | A | NULL | | 2 | B | NULL | | 3 | C | NULL | | 4 | D | NULL | | 5 | E | NULL | | 6 | F | NULL | | 7 | G | NULL | | 8 | H | NULL | | 9 | I | NULL | | 10 | J | NULL | | 11 | K | NULL | +-----------+------+------+ 11 rows in set (0.00 sec) mysql> UPDATE players SET team = RAND(); Query OK, 11 rows affected (0.00 sec) Rows matched: 11 Changed: 11 Warnings: 0 mysql> SELECT * FROM players; +-----------+------+------+ | player_id | name | team | +-----------+------+------+ | 1 | A | 0 | | 2 | B | 0 | | 3 | C | 1 | | 4 | D | 1 | | 5 | E | 0 | | 6 | F | 1 | | 7 | G | 1 | | 8 | H | 1 | | 9 | I | 1 | | 10 | J | 1 | | 11 | K | 0 | +-----------+------+------+ 11 rows in set (0.00 sec) So running the rest of your code updates the zeros to ones, putting everyone in team 1. See you back at the drawing board
  16. Barand
    What version of MySQL? https://blogs.oracle.com/MySqlOnWindows/entry/milliseconds_value_support_on_datetime
  17. Barand
    I do not see session_start() on either page. It needs to be called at the top of every page that uses session variables.
  18. Barand
    try $db = new mysqli(HOST,USERNAME,PASSWORD,'test'); $res = $db->query("SELECT COUNT(DISTINCT name) FROM toastie"); list($count) = $res->fetch_row(); $N = ceil($count/5); $db->query("UPDATE toastie SET groupnumber = FLOOR(1 + RAND() * $N)"); Gives
  19. Barand
    The only thing that gets executed in your while loop is the bit between while(..) and the ";" at the end of that line. In other words - nothing. try while( $row = sqlsrv_fetch_array( $result, SQLSRV_FETCH_NUMERIC) ) { $encodedarray = array_map(utf8_encode, $row); echo json_encode($encodedarray); }
  20. Barand
    A couple of other things Dont use SELECT * You only select 1 record, so don't need while loop Your update should use the primary key to select the record You should be using mysqli or PDO, not mysql_ functions $SQLproverb = "SELECT id, proverb FROM $Proverb ORDER BY RAND() LIMIT 1"; $QueryResult = mysql_query($SQLproverb, $DBConnect); $Row = mysql_fetch_assoc($QueryResult); echo "<p style = 'text-align:center'>{$Row[proverb]}</p>\n"; $SQLString = "UPDATE randomproverb SET display_count = display_count + 1 WHERE id = {$Row[id]}"; $QueryResult = mysql_query($SQLstring, $DBConnect);
  21. Barand
    You could use SQL's LEAST() function also ... SET ap = ap + LEAST(100-ap, 10) ... so if ap is 93 then only 7 is added but if ap is <91 then 10 is added.
  22. Barand
    Granularity determines the smallest interval you are interested in. If an event occurred 3 days 5 hours 12 minutes 25 seconds ago would you want to show that, or would it be 3 days ago 3 days 5 hours ago 3 days 5 hours 12 minutes ago You might just want 3 days, but what if the event was only 5 hours 12 minutes 25 seconds ago, what then? 5 hours ago? today? Have a look at DateTime (particulary DateTime::diff() ) and DateInterval classes.
  23. Barand
    You could do "SELECT COUNT(DISTINCT DATE_FORMAT(date, '%Y-%m')) ..." Or, perhaps, "SELECT DISTINCT DATE_FORMAT(date, '%Y-%m') as month ..." and instead of giving page numbers to click on, give the months 14-12 14-11 14-10 14-09 ... 14-01 Then instead of using page numbers and LIMIT clauses just query for the Y-m that is clicked
  24. Barand
    Just do this <?php $id = $_GET['id']; $data = $_GET['data']; $key = "Password"; mysql_query("update contest_data set `$key` = '$data' where Contest_Data_ID='$id'") ?>
  25. Barand
    As you were told in the first reply to this post
×
×
  • 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.