Barand
-
Posts
24,423 -
Joined
-
Last visited
-
Days Won
806
Posts posted by Barand
-
-
1 minute ago, tryingphp said:
$sql = "UPDATE FROM users WHERE user_id=user_id";
I can see I have been talking to myself. I have better things to than waste more time on you.
-
1
-
-
One way would be to add an "expiry_date" (default NULL) column to your user table.
Instead of deleting the record, update the record setting the expiry date to CURRENT_DATE+3 days.
Run a job every day that does a "DELETE FROM user WHERE expiry_date < CURRENT_DATE
-
At the moment, your query says
"DELETE all records from the user table where the value in column "user_id" is equal to the value in column "user_id".
Is it obvious to you yet why every record gets deleted?
You need to fix your query so it compares the value in column user_id against your session value.
-
I suggest you read your query carefully, bearing in mind that the condition "user_id = user_id" is true for all records.
-
When your button's name is "delete" why are you checking for $_REQUEST["remove_$i"] instead of $_REQUEST['delete'] ?
Stop using REQUEST. Use POST or GET depending on your form's method.
if you are fetching data to display, use method GET. If submitting your form has consequences (such as updating, deleting, emailing) then use POST method.
-
For the record, the problem is an extra heading column, not an extra data column. You have <th> intead of a </th> thus adding an extra header cell.
<th>VISITOR DOMAIN ADDRESS<th> ^
-
Those ini-set()s at the beginning need to be in your php.ini file.
If you have startup errors the code isn't executed, so how can it then set and report startup errors???
-
An alternative is store the data in an array and sort the array. Pagination can be achieved using array_slice().
Example...
<?php /* TEST DATA CREATE TABLE `employee` ( `empid` int(11) NOT NULL AUTO_INCREMENT, `fname` varchar(50) DEFAULT NULL, `lname` varchar(50) DEFAULT NULL, `dob` date DEFAULT NULL, PRIMARY KEY (`empid`) ) INSERT INTO `employee` VALUES (1,'Peter','Smith','1985-01-26'), (2,'Paul','Hartley','1973-12-02'), (3,'Mary','Baker','1980-04-11'), (4,'Jane','Doe','1990-11-28'); */ // GET DATA (assumes PDO connection) $res = $db->query("SELECT empid as `Employee ID` , fname as `First Name` , lname as `Last Name` , dob as `Date of Birth` FROM employee "); $data = $columns = []; $row = $res->fetch(); $columns = array_keys($row); do { $data[] = $row; } while ($row = $res->fetch()); // SORT THE DATA $sortby = $_GET['sortby'] ?? 'Employee ID'; $desc = $_GET['desc'] ?? 0; $chk = $desc==1 ? 'Checked' : ''; usort($data, function($a, $b) use ($sortby, $desc) { if ($desc) return $b[$sortby] <=> $a[$sortby]; else return $a[$sortby] <=> $b[$sortby]; }); // TABLE HEADINGS $theads = '<tr style="background-color: #EEE; font-weight: 600"><td>' . join('</td><td>', $columns) . "</td></tr>\n"; // TABLE DATA $tdata = ''; foreach ($data as $d) { $tdata .= "<tr><td>" . join('</td><td>', $d) . "</td></tr>\n"; } // SORT OPTIONS function sortOptions($columns, $current) { $opts = ''; foreach ($columns as $c) { $sel = $c==$current ? 'selected' : ''; $opts .= "<option $sel>$c</option>\n"; } return $opts; } ?> <html> <head> <title>Sample data sort</title> </head> <body> <form> <fieldset> Sort by <select name='sortby' onclick="this.form.submit()"> <?=sortoptions($columns, $sortby)?> </select> DESC <input type="checkbox" name="desc" value="1" <?=$chk?> onclick="this.form.submit()"> </fieldset> </form> <table style="width:80%; margin: 30px auto; font-family: sans-serif;"> <?=$theads?> <?=$tdata?> </table> </body> </html>
-
What happens if you actually test if the execute() worked?
Instead of
$subscribe->execute([$name, $email]); if($subscribe){ echo ....
try
if ($subscribe->execute([$name, $email])) { echo ....
-
Fred's id is 1, therefore
listStaff(1, $users);
-
If your problem is how to output tab separated data with column names
/* SAMPLE DATA +-------+-------+---------+------------+ | empid | fname | lname | dob | +-------+-------+---------+------------+ | 1 | Peter | Smith | 1985-01-26 | | 2 | Paul | Hartley | 1973-12-02 | | 3 | Mary | Baker | 1980-04-11 | | 4 | Jane | Doe | 1990-11-28 | +-------+-------+---------+------------+ */ $res = $db->query("SELECT empid , fname , lname , dob FROM employee; "); echo '<pre>'; $row = $res->fetch(PDO::FETCH_ASSOC); echo join("\t", array_keys($row)) . "\n"; // headings do { echo join("\t", $row) . "\n"; // data } while ($row = $res->fetch()); echo '</pre>';
Which gives
Similarly, if you want to write it to a csv file for export to Excel, then
$res = $db->query("SELECT empid , fname , lname , dob FROM employee; "); $fp = fopen('AAA.csv', 'w'); $row = $res->fetch(PDO::FETCH_ASSOC); fputcsv($fp, array_keys($row), "\t"); // headings do { fputcsv($fp, $row, "\t"); // data } while ($row = $res->fetch());
-
1
-
-
2 hours ago, kilnakorr said:
Can you explain why ids would be a better choice?
- Data normization
- Efficiency
- Spelling mistakes
-
Use ids, not names, to link records.
TABLE: user +----+----------+------------+ | id | username | leader_id | +----+----------+------------+ | 1 | fred | 2 | | 2 | mo | NULL | | 3 | brian | 2 | | 4 | john | 2 | | 5 | peter | 1 | | 6 | curly | 1 | | 7 | joan | 1 | | 8 | Dennis | 6 | +----+----------+------------+
Recursion is your friend here.
$res = $db->query("SELECT id , username , leader_id FROM usertest order by leader_id "); $users = []; // store users in array for each leader foreach ($res as $r) { $users[$r['leader_id']][] = [ 'id' => $r['id'], 'username' => $r['username'] ]; } echo '<pre>'; listStaff(null, $users, 0); // list staff for leader "null" echo '</pre>'; /** * recursive function to list staff * * @param int $id * @param array $users * @param int $level */ function listStaff($id, &$users, $level=0) { $indent = str_repeat("\t", $level); foreach ($users[$id] as $u) { // for each of their staff echo "$indent{$u['username']}<br>"; // outout the name if (isset($users[$u['id']])) { // if they have staff listStaff($u['id'], $users, $level+1); // list their staff } } }
Giving
mo fred peter curly Dennis joan brian john
-
1
-
-
Input data (horsetest)
+----+---------+------+------+----------+------------+ | id | name | sire | dam | gender | dob | +----+---------+------+------+----------+------------+ | 1 | Horse A | NULL | NULL | STALLION | 2005-01-15 | | 2 | Horse B | NULL | NULL | MARE | 2005-03-19 | | 3 | Horse C | NULL | NULL | MARE | 2006-03-11 | | 4 | Horse D | 1 | 2 | STALLION | 2009-05-10 | | 5 | Horse E | 1 | 3 | MARE | 2010-08-25 | +----+---------+------+------+----------+------------+
then to get mother and father
SELECT h.id , h.name , h.gender , TIMESTAMPDIFF(YEAR, h.dob, CURDATE()) as age , h.sire as f_id , s.name as father , h.dam as m_id , d.name as mmother FROM horsetest h JOIN horsetest s ON h.sire = s.id JOIN horsetest d ON h.dam = d.id;
giving
+----+---------+----------+------+------+---------+------+---------+ | id | name | gender | age | f_id | father | m_id | mmother | +----+---------+----------+------+------+---------+------+---------+ | 4 | Horse D | STALLION | 10 | 1 | Horse A | 2 | Horse B | | 5 | Horse E | MARE | 9 | 1 | Horse A | 3 | Horse C | +----+---------+----------+------+------+---------+------+---------+
-
There are examples here
-
-
The basic processing is (pseudocode)
function tree (id) { get id, name, sire, dam for id if not found return endif output id/name tree(sire) tree(dam) } // output pedigree tree for for horse X tree(X) -
About 15 years ago I wrote a function to produce this
from data very much like yours EG
SELECT * FROM horse; +----+------+------+------+----------+ | id | name | sire | dam | gender | +----+------+------+------+----------+ | 26 | A | 27 | 39 | STALLION | | 27 | B | 28 | 40 | STALLION | | 28 | C | 29 | 40 | STALLION | | 29 | D | 30 | 40 | STALLION | | 30 | E | 31 | 41 | STALLION | | 31 | F | 32 | 41 | STALLION | | 32 | G | 33 | 42 | STALLION | | 33 | H | 33 | 42 | STALLION | | 34 | I | 35 | 43 | STALLION | | 35 | J | 36 | 43 | STALLION | | 36 | K | 37 | 44 | STALLION | | 37 | L | 38 | 44 | STALLION | | 38 | M | 34 | 45 | STALLION | | 39 | N | 29 | 46 | MARE | | 40 | O | 27 | 51 | MARE | | 41 | P | 28 | 51 | MARE | | 42 | Q | 29 | 50 | MARE | | 43 | R | 30 | 50 | MARE | | 44 | S | 31 | 49 | MARE | | 45 | T | 32 | 49 | MARE | | 46 | U | 33 | 48 | MARE | | 47 | V | 34 | 48 | MARE | | 48 | W | 35 | 46 | MARE | | 49 | X | 36 | 46 | MARE | | 50 | Y | 37 | 47 | MARE | | 51 | Z | 38 | 47 | MARE | +----+------+------+------+----------+
It uses a recursive* function which gets the sire and dam for the horse $id and outputs the name. It then calls itself for the sire and the dam which outputs their names and get their parents. This is repeated until the specified level of ancestry is reached.
Is that the sort of thing you are trying to do?
If so, give it a go using the method I described and come back if oyu get stuck
* recursion : see recursion
-
1
-
-
I find it easier to get options from a function, then the current value (in this case the "$user_role") can be passed as a parameter
function roleOptions($conn, $current) { $sql = "SELECT role_id , role_name FROM ssm_role ORDER BY role_name ASC"; $opts = ''; if($result = mysqli_query($conn, $sql)) { if (mysqli_num_rows($result)>0){ while ($row = mysqli_fetch_array($result)){ $sel = $row['role_id'] == $current ? 'selected' : ''; $opts .= "<option $sel value='".$row['role_id']."'>".$row['role_name']."</option>"; } } } return $opts; }
HTML
<select name="role"> <?= roleOptions($conn, $user_role) ?> </select>
-
OK, so if instead of 1 action you have three separate scripts specified as actions for the form - but you still have to write the code to do those actions. Each of those three would have to do step 1 (get the data) thus tripling that code.
If one step fails, do you still want too do the others? How do you control that if they are now separated?
What would you gain?
-
Places to look for each of the above steps,,,
-
Remove the comma before the WHERE
-
You could take the code in your first post, make it a function and use the " wash, rinse, repeat" approach.
-
Also, your javascript looks decidedly dodgy to me.
- Two functions called on same event???
- They refer to objects with id = 'det_X' -- you have no objects with ids beginning with "det_"
$_SESSION not setting in my else
in PHP Coding Help
Posted
You can check the location of the php.ini file being used in the first section of the output from
EG