Jump to content

Barand

Moderators
  • Content Count

    20,819
  • Joined

  • Last visited

  • Days Won

    481

Barand last won the day on May 23

Barand had the most liked content!

Community Reputation

1,509 Excellent

About Barand

  • Rank
    Sen . ( ile || sei )

Profile Information

Recent Profile Visitors

63,021 profile views
  1. I didn't say is was right - it's just what was in the code I was condensing to illustrate the nested loops.
  2. Use explicit joins FROM A JOIN B ON ... rather than confusing the issue by putting the join conditions in a WHERE clause (less efficient too). Also there may be employee/s at the top of the management tree without a manager. With my LEFT JOIN these would still appear, with w3fools's version they would not.
  3. It really helps if you tell what "doesn't work" means, otherwise it tells us nothing. Although, glancing at that query, try this revised version $sql = "SELECT * FROM `comments` LEFT JOIN `artikelen` ON comments.nummer= artikelen.nummer AND artikelen.nummer = $articlenummer ORDER BY artikelen.nummer";
  4. Assign role to users Have an access table to define which roles can access which files (As requested, all tables are accessible via PDO) +-------------+ +---------------+ +--------------+ | user | | role | | file | +-------------+ +---------------+ +--------------+ | user_id | +------| role_id |-+ +---| file_no | | username | | | description | | | | filename | | password | | +---------------+ | | +--------------+ | role_id |>-----+ | | +-------------+ | | | +-------------+ | | | access | | | +-------------+ | +--<| role_id | | | file_no |>-+ +-------------+
  5. This summarises a section of your code ... for($j='2018';$j<=$maxdate;$j++) { $q="select id from seasonstats where date >='$session_value' and date <='$session1_value' order by date"; while($r=mysqli_fetch_object($qr)) { $q="select distinct(_player_id) from seasonteamstats where matchid in ($match_id_list) order by lastname"; while($r=mysqli_fetch_object($qr)) { foreach($players_ids as $var=>$val) { $q="select * from seasonteamstats where _player_id='$val' and matchid in ($match_id_list) order by lastname"; } } } } Do you find it takes a while to run? You need to learn to use JOINs and you can do all this in one efficient query. The golden rule is "Don't run queries inside loops"
  6. The ghost of Jacques still walks these corridors
  7. There are some options that you should set when you connect to the database server. For example, this my included connection file const HOST = 'localhost'; const USERNAME = '???'; const PASSWORD = '???'; const DATABASE = '???'; function pdoConnect($dbname=DATABASE) { $db = new PDO("mysql:host=".HOST.";dbname=$dbname;charset=utf8",USERNAME,PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); return $db; } $conn = pdoConnect() will then connect to my default database, but if I set up a test database "bintech" to put your tables in for testing code, then I can call $conn = pdoConnect('bintech'). Your UPDATE statement is wrong as it will update all records for which "lockup_id = lockup_id", which is all of them. You are missing the ":"
  8. Join the table to itself SELECT emp.name as empname , boss.name as bossname FROM employee as emp LEFT JOIN employee as boss ON emp.boss_id = boss.id;
  9. I showed you how to use a prepared query, rewriting your code for you here Read the replies.
  10. Are you sure you have matching postcode values? No hidden whitespace. And records that match the lidnummer?
  11. I am wondering if the lack of primary keys in your tables is a contributing factor Set up the tables and tried it - deletion worked when run manually. mysql> insert into lid values -> (1, 'A', 'B','C','D'), -> (2, 'A', 'B','F','D'); Query OK, 2 rows affected (0.07 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> insert into postcode values -> ('C', 'aaa', 'bbb'), -> ('F', 'ccc', 'ddd'); Query OK, 2 rows affected (0.04 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> DELETE lid, postcode FROM lid INNER JOIN postcode WHERE lid.postcode = postcode.postcode AND lid.lidnummer = 2; Query OK, 2 rows affected (0.05 sec) mysql> select * from lid; +-----------+----------+------------+----------+------------+ | lidnummer | voornaam | achternaam | postcode | huisnummer | +-----------+----------+------------+----------+------------+ | 1 | A | B | C | D | +-----------+----------+------------+----------+------------+ 1 row in set (0.00 sec) mysql> select * from postcode; +----------+-------+------------+ | postcode | adres | woonplaats | +----------+-------+------------+ | C | aaa | bbb | +----------+-------+------------+ 1 row in set (0.00 sec)
  12. Can you post the structures of your "lid" and "postcode" tables for us.
  13. The purpose of using prepared statements is so you don't put values directly into the query itself. Instead you use a "placeholder" for the value and pass the value in the execute statement. $query = " SELECT * FROM task_list WHERE user_id = ? ORDER BY task_list_id DESC "; $statement = $connect->prepare($query); $statement->execute( [ $_SESSION["user_id"] ] ); There is another commonly used convention when using a database: if you want to produce a list of data items from a table then execute a query against that table. Querying the task_list table in order to get a list of lockups ain't going to work too well.
×
×
  • 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.