Barand
-
Posts
24,511 -
Joined
-
Last visited
-
Days Won
819
Posts posted by Barand
-
-
Post the full error message too
-
I suspect this is the code causing the error
// do other tasks -
Shame on me for missing that!
@techker To save having to test if every mysqli function call worked or not, tell it to throw an exception automatically. It keeps your code a lot cleaner. Call mysqli_report() before you connect to the db.
mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT); $mydb = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);
-
I see nothing in that code that would give that error.
FYI. don't put a single string variable inside quotes as you have
$end = new DateTime("$datestop"); ^ ^It's inefficient and unnecessary. Just use
$end = new DateTime($datestop); -
Are you saying that when you select "1" from the dropdown then $_GET['cr'] contains "2"
You don't show your <select> tag - can you confirm the name = 'cr' ?
It looks OK but check the source code of your form page to make sure the menu is built correctly.
Some general points …
- As you are updating data your form method should be POST and not GET
- strip_tags hasn't been required since magic_quotes were deprecated decades ago
- mysqli_real_escape_string is not used with prepared queries,
-
FYI, if you are considering the JSON route suggested by @requinix there is a MySQL example here
-
So I guess you'd have a front-end form for the user so they can select various options ( varying depending on the survey) and then submission of the form would generate the above query and execute it.
The challenge then becomes the program which, given a specific survey, will generate that front-end form.
-
You don't show the query so I'm going to guess the results are from using a join.
If you have
tableA tableB +-----------+--------------+ +------+-----------+-------+ | a_id | name | | b_id | cola | a_id | +-----------+--------------+ +------+-----------+-------+ | 1 | Curly | | 1 | aaa | 1 | | 2 | Larry | | 2 | bbb | 2 | +-----------+--------------+ | 3 | ccc | 1 | | 4 | ddd | 1 | | 5 | eee | 1 | | 5 | fff | 2 | | 5 | ggg | 2 | | 5 | hhh | 2 | +------+-----------+-------+ SELECT a.name , b_id , cola FROM tablea a INNER JOIN tableb b ON a.a_id = b.a_id WHERE a.a_id = 1then the results will be
+---------+--------+--------+ | name | b_id | cola | +---------+--------+--------+ | Curly | 1 | aaa | | Curly | 3 | ccc | | Curly | 4 | ddd | | Curly | 5 | eee | +---------+--------+--------+
where "Curly" is repeated 4 times because for the record in tablea there are 4 matching records in tableb with matching values in column a_id.
That's the nature of joins. Live with it.
A common way is to rearrange the data thus
data['Curly'] = [ 1 => aaa, 3 => ccc, 4 => ddd, 5 => eee ] -
The simple part is creating an application to create different surveys. The hard part is an app for analyzing the results of any survey that is thrown at it.
You are going to have infinite combinations of different types of answer
- simple Boolean (Y/N)
- single value number/date/text(avoid)
- single choice list
- multiple choice list
- value ranges (crap, poor, OK, good, excellent)
with
- cross-tabulations/pivot tables between any two responses (eg average/count/total of X for each Y)
- Frequency charts to analyze responses
I suppose it's a bit like printers (sell it cheap and make your money selling the ink cartridges). In this case you sell the ability to create surveys then monetize it by selling customised analysis services.
Something like this should work
-
This is my test version (with a couple of extra displays to confirm the processing)
<?php ## connect to your DB here ## $userid = '0'; $subid = '0'; $cr = 0; $max = 0; $confirm = ''; $before = $after = ''; // // was data posted? // if ($_SERVER['REQUEST_METHOD']=='POST') { $userid = $_POST['userid'] ?? 0; $subid = $_POST['subid'] ?? 0; $cr = $_POST['credits'] ?? 0; $before = "<h3>Before</h3>" . query2HTML($db, "select * from codes where userid in ($userid, $subid)"); $stmt = $db->prepare("UPDATE codes SET UserId = ? WHERE UserId = ? AND status != 'Used' LIMIT ? "); $stmt->bind_param('iii', $subid, $userid, $cr); $stmt->execute(); $howmany = $db->affected_rows; if ($cr==$howmany) { $confirm = "<div style='display:inline-block; padding: 16px; background-color: #EEE; color: #000'>$howmany rows transferred</div><br>"; } else { $confirm = "<div style='display:inline-block; padding: 16px; background-color: #F00; color: #FFF'>$howmany rows transferred</div><br>"; } $after = "<h3>After</h3>" . query2HTML($db, "select * from codes where userid in ($userid, $subid)"); } // // respond to ajax request // if (isset($_GET['ajax'])) { exit(cr_options($db, $_GET['userid'], 0)); } /** * create list of credit quantity options * * @param mysqli $db * @param int $user * @param int $current - current quantity */ function cr_options(mysqli $db, $user, $current) { $stmt = $db->prepare("SELECT COUNT(*) as tot FROM codes WHERE status != 'Used' AND UserId = ? "); $stmt->bind_param('i', $user); $stmt->execute(); $stmt->bind_result($tot); $stmt->fetch(); if ($tot == 0) { $opts = "<option value=''>No unused codes</option>"; } else { for($i=1; $i<=$tot; $i++) { $sel = $i==$current ? 'selected' : ''; $opts .= "<option $sel>$i</option>"; } } return $opts; } /** * general purpose debugging utility function to output query results * * @param mysqli connection $db * @param string $sql * @return string - html table */ function query2HTML($db, $sql) { $output = "<table border='1' cellpadding='2' style='border-collapse:collapse'>\n"; // Query the database $result = $db->query($sql); // check for errors if (!$result) return ("$db->error <pre>$sql</pre>"); if ($result->num_rows == 0) return "No matching records"; // get the first row and display the headings $row = $result->fetch_assoc(); $output .= "<tr><th>" . join('</th><th>', array_keys($row)) . "</th></tr>\n"; // display the data do { $output .= "<tr><td>" . join('</td><td>', $row) . "</td></tr>\n"; } while ($row = $result->fetch_assoc()); $output .= "</table>\n"; return $output; } ?> <html> <head> <meta http-equiv="content-language" content="en"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <title>Sample Code Transfer</title> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script> <script type="text/javascript"> $().ready(function() { $("#userid").change( function() { var user = $(this).val() $.get( "", {"ajax":1, "userid":user }, function(resp) { $("#credits").html(resp) }, "TEXT" ) }) }) </script> <style type="text/css"> label {display:inline-block; width: 160px; font-weight: 600;} </style> </head> <body> <form method='post'> <fieldset> <legend>Codes Transfer</legend> <label>From user</label><input type="text" name="userid" id="userid" value="<?=$userid?>" size="5"><br> <label>Credits to transfer</label><select name="credits" id="credits"><?=cr_options($db, $userid, $cr)?></select><br><br> <label>To user</label><input type="text" name="subid" id="subid" value="<?=$subid?>" size="5"><br> <label> </label><input type="submit" name="btnSub" value="Transfer"> </fieldset> </form> <br> <br> <?=$before?> <?=$confirm?> <?=$after?> </body> </html>
-
What's wrong with one form and let the user select from a choice of 4 options?
-
6 minutes ago, StevenOliver said:
There's nothing on the 'net like this
There is an example of GROUP BY and GROUP_CONCAT in the SQL tutorial in my sig
-
2 minutes ago, StevenOliver said:
I don't know how you know this stuff.
It's all in the manual
-
try
/* INPUT mysql> select * from oliver; +---------+--------+------+ | item | colour | num | +---------+--------+------+ | Hammer | red | 5555 | | Pliers | blue | 2222 | | Pliers | blue | 1111 | | Pliers | blue | 8888 | | Goggles | yellow | 6666 | +---------+--------+------+ */ $res = $db->query("SELECT item , colour , GROUP_CONCAT(num SEPARATOR ', ') as nums FROM oliver GROUP BY item, colour "); $output = ''; foreach ($res as $r) { $output .= "{$r['item']}, {$r['colour']}, {$r['nums']}\n"; } file_put_contents("document.txt", $output);
output
Goggles, yellow, 6666 Hammer, red, 5555 Pliers, blue, 2222, 1111, 8888
-
I have no idea what "doesn't work" means.
-
Still working for me with your table format...
/* BEFORE +-----+-------+------------+--------+--------+----------+ | CID | Value | DateIn | Status | UserID | DateSold | +-----+-------+------------+--------+--------+----------+ | 1 | 24242 | 2019-11-11 | 0 | 5 | NULL | | 2 | 24243 | 2019-11-11 | 0 | 5 | NULL | | 3 | 24244 | 2019-11-11 | 0 | 5 | NULL | | 4 | 24245 | 2019-11-11 | 0 | 3 | NULL | | 5 | 24246 | 2019-11-11 | 0 | 3 | NULL | | 6 | 24246 | 2019-11-11 | 0 | 4 | NULL | | 7 | 24246 | 2019-11-11 | 0 | 4 | NULL | +-----+-------+------------+--------+--------+----------+ */ $userid = 5; $subid = 3; $cr = 2; $stmt = $db->prepare("UPDATE codes SET UserId = ? WHERE UserId = ? LIMIT ? "); $stmt->bind_param('iii', $subid, $userid, $cr); $stmt->execute(); /* AFTER +-----+-------+------------+--------+--------+----------+ | CID | Value | DateIn | Status | UserID | DateSold | +-----+-------+------------+--------+--------+----------+ | 1 | 24242 | 2019-11-11 | 0 | 3 | NULL | <-- changed user 5 to user 3 | 2 | 24243 | 2019-11-11 | 0 | 3 | NULL | <-- changed user 5 to user 3 | 3 | 24244 | 2019-11-11 | 0 | 5 | NULL | | 4 | 24245 | 2019-11-11 | 0 | 3 | NULL | | 5 | 24246 | 2019-11-11 | 0 | 3 | NULL | | 6 | 24246 | 2019-11-11 | 0 | 4 | NULL | | 7 | 24246 | 2019-11-11 | 0 | 4 | NULL | +-----+-------+------------+--------+--------+----------+ */
Are you sure your $_POST contains what you think it should contain?
-
That is precisely what the last example query that I posted is doing (except in mine it was user 1 giving 5 codes to user 123)
-
What does a sample of your data look like before the query and what should it look like after?
I showed you mine, you show me yours.
-
1
-
-
Well, I did ask and didn't tell me so I won't waste any more of my time LOL.
-
You seem intent on keeping the table secret so I'll guess.
/* BEFORE +----------+--------+------+ | codes_id | UserId | code | +----------+--------+------+ | 1 | 1 | aa | | 2 | 1 | bb | | 3 | 1 | cc | | 4 | 1 | dd | | 5 | 1 | ee | | 6 | 1 | ff | | 7 | 1 | gg | | 8 | 1 | hh | | 9 | 1 | jj | | 10 | 1 | kk | +----------+--------+------+ */ $userid = 1; $subid = 123; $cr = 5; $stmt = $db->prepare("UPDATE codes SET UserId = ? WHERE userid = ? LIMIT ? "); $stmt->bind_param('iii', $subid, $userid, $cr); $stmt->execute(); /* AFTER +----------+--------+------+ | codes_id | UserId | code | +----------+--------+------+ | 1 | 123 | aa | | 2 | 123 | bb | | 3 | 123 | cc | | 4 | 123 | dd | | 5 | 123 | ee | | 6 | 1 | ff | | 7 | 1 | gg | | 8 | 1 | hh | | 9 | 1 | jj | | 10 | 1 | kk | +----------+--------+------+ */ -
What does your codes table look like?
-
13 hours ago, dil_bert said:
here i rely on a great thread - found on the pyhon-developer-page: https://python-forum.io/Thread-VS-Code-from-start?highlight=VSCode
This begs the question, "Why the phuk are you boring us to death here, on what is basically a PHP site, with all this Python stuff when you could be doing it to the members of "python-forum.io?"
-
1
-
-
-
If I read it correctly, you start with a table like this
test_fruit +----+------------+ | id | name | +----+------------+ | 1 | banana | | 2 | apple | | 3 | strawberry | | 4 | lemon | | 5 | blueberry | | 6 | strawberry | | 7 | melon | | 8 | apple | | 9 | strawberry | +----+------------+and want to end up with "document.txt" like this
apple 1 and 2 banana 1 blueberry 1 lemon 1 melon 1 strawberry 1 and 2 and 3
Code
$res = $db->query("SELECT name , COUNT(*) as tot FROM test_fruit GROUP BY name "); $output = ''; foreach ($res as $r) { $output .= $r['name'] . ' ' . join(' and ', range(1, $r['tot'])) . "\n"; } file_put_contents("document.txt", $output);
DateTime could not be converted to string
in PHP Coding Help
Posted
You have removed the "new"s
Should be
$begin = new DateTime($today); $end = new DateTime($datestop);BTW error messages have line numbers to tell you where it happened.