Adamhumbug

Yeah its very strange, when i change that one section to use <<<TEXT several areas of my code are now throwing Parse error: syntax error, unexpected '' (T_ENCAPSED_AND_WHITESPACE), expecting '-' or identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING) in /homepages/29/d742272110/htdocs/user-list.php on line 200 I had it on this line echo "<option value='{$row['user_level_id']}'>{$row['user_level_name']}</option>"; which was concatenated and i have now chnaged but i am now getting it on this line <?php $uid = $_SESSION['user_id']; //THIS LINE $stmt = $conn->prepare("SELECT user_firstname, user_lastname FROM ssm_user WHERE user_id = ?"); $stmt -> bind_param('i', $uid); $stmt -> execute(); $stmt -> store_result(); $stmt -> bind_result($fname, $lname); $stmt -> fetch(); ?>

Hi Barand, great help as ever. When i make the change using your first suggestion i get the following error. Parse error: syntax error, unexpected '' (T_ENCAPSED_AND_WHITESPACE), expecting '-' or identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING) in /homepages/29/d742272110/htdocs/user-list.php on line 132 $sql = "SELECT role_id, role_name FROM ssm_role ORDER BY role_name ASC"; if($result = mysqli_query($conn, $sql)){ if (mysqli_num_rows($result)>0){ while ($row = mysqli_fetch_array($result)){ echo "<option value='".$row['role_id']."'>".$row['role_name']."</option>"; //line 132 } } } I am in the process of changing all my php to use prepared stmts but have not got to this one yet.

Hi Psyco, echo "<td><a class='btn btn-primary col-sm-12' data-toggle='modal' data-userid='" . $uid . "' href='#userModal' data-firstname='" . $ufn ."' data-lastname='". $uln."' data-email='" . $ue . "' data-accountlevel='" . $ualid . "' data-mobile='".$um ."'data-role='".$urid."' data-active-sheets='".$ename."'>Manage</a></td>"; This is copied directly from my code This is copied from inspector in Firefox for mac. When i take the ' out of Chelsea's i get the following <a class="btn btn-primary col-sm-12" data-toggle="modal" data-userid="2" href="#userModal" data-firstname="Chelsea" data-lastname="Hockley" data-email="[email protected]" data-accountlevel="1" data-mobile="07783544882" data-role="1" data-active-sheets="A new event, Chelseas Event">Manage</a>

Hi All, I have done a select from my DB and the data contains a '. echo "<td><a class='btn btn-primary col-sm-12' data-toggle='modal' data-userid='" . $uid . "' href='#userModal' data-firstname='" . $ufn ."' data-lastname='". $uln."' data-email='" . $ue . "' data-accountlevel='" . $ualid . "' data-mobile='".$um ."'data-role='".$urid."' data-active-sheets='".$ename."'>Manage</a></td>"; outputs <a class="btn btn-primary col-sm-12" data-toggle="modal" data-userid="2" href="#userModal" data-firstname="Chelsea" data-lastname="Hockley" data-email="[email protected]" data-accountlevel="1" data-mobile="0774882" data-role="1" data-active-sheets="A new event,Chelsea" s="" event'="">Manage</a> the issue part data-active-sheets="A new event,Chelsea" s="" event'="" Should be data-active-sheets="A new event,Chelsea's event" How do i prevent the ' from causing me this issue?

Perfect, thanks you. This did it SELECT user_id, user_email, user_firstname, user_lastname, user_mobile, user_role_id, role_name, user_account_level_id, user_level_name, GROUP_CONCAT(job_eventname) as eventname from ssm_user left join ssm_role on role_id = user_role_id left join ssm_user_level on user_account_level_id = user_level_id left join ssm_job on job_manager_id = user_id Group by user_id

HI All, I have the following qry SELECT user_id, user_email, user_firstname, user_lastname, user_mobile, user_role_id, role_name, user_account_level_id, user_level_name, job_eventname from ssm_user left join ssm_role on role_id = user_role_id left join ssm_user_level on user_account_level_id = user_level_id left join ssm_job on job_manager_id = user_id ORDER BY user_id; Which outputs user_id user_email user_firstname user_lastname user_mobile user_role_id role_name user_account_level_id user_level_name job_eventname 1 [email protected] Adam Hewitt 07783598021 5 Director 6 System Super User 2 [email protected] Chelsea Hockley 077835482 1 Manager 1 Regular User A new event 2 [email protected] Chelsea Hockley 077834882 1 Manager 1 Regular User Chelsea's Event 8 [email protected] First Name Last Name 07783598021 2 Chef 1 Regular User 9 [email protected] Steve Jones 07734518273 2 Chef 2 No System Access 10 [email protected] Sally Lawrence 07890736490 2 Chef 2 No System Access I would like "a new event" and "chelsea's event" to be in an array as all of the other information is the same, how would i go about doing this?

I didnt think about that but it totally makes sense - Thanks again

Thank you for this, i took your advice and used the following to get what i needed. select a.job_id, a.job_eventname, a.job_date, a.job_end_date, b.customer_companyname, c.venue_name, d.user_firstname as managerf, d.user_lastname as managerl, e.user_firstname as cheff, e.user_lastname as chefl from ssm_job a inner join ssm_customer b on a.job_customer_id=b.customer_id inner join ssm_venue c on a.job_venue_id = c.venue_id left join ssm_user d on a.job_manager_id = d.user_id left join ssm_user e on a.job_head_chef_id = e.user_id

Hi, I am running a query in which the result set contains the ids of 2 users whose names are stored in the user table. I want to get both of their names but not sure how i would go about this. select a.job_manager_id, a.job_head_chef_id,a.job_id, a.job_eventname, a.job_date, a.job_end_date, b.customer_companyname, c.venue_name from ssm_job a inner join ssm_customer b on a.job_customer_id=b.customer_id inner join ssm_venue c on a.job_venue_id = c.venue_id Give me the results 2 9 35 Chelsea's Event 2019-12-23 2019-12-27 Cavuto, John A A Corporation both the 2 and the 9 in the results refer to users. In the users table (ssm_users) they have user_firstname and user_lastname How can i return both of their full names here. Happy to provide more structure if required. Kind Regards Adam

I would have to change my database type from MyIsam to innoDB - is there any reason why i shouldnt do that?

I actually fixed this by simply changing the following from x.dataset to x[0].dataset

Hi, i am populating a modal and from this modal, another modal is populated to confirm a delete. I populate the first modal using button.data I need the user id to be put onto the delete button in the first modal so it can be passed to the second. The following is how i build the modal: $('#userModal').on('show.bs.modal', function(event) { // Button that triggered the modal var button = $(event.relatedTarget) //data elements var userid = button.data('userid') var firstname = button.data('firstname') var lastname = button.data('lastname') var email = button.data('email') var level = button.data('accountlevel') var mobile = button.data('mobile') var role = button.data('role') var modal = $(this) modal.find('.modal-title').text('Update User') modal.find('.modal-body #UM-uid').val(userid) modal.find('.modal-body #UM-firstname').val(firstname) modal.find('.modal-body #UM-lastname').val(lastname) modal.find('.modal-body #UM-emailAddress').val(email) modal.find('.modal-body #UM-accountLevelId').val(level) modal.find('.modal-body #UM-mobileNumber').val(mobile) modal.find('.modal-body #UM-roleId').val(role) var x = modal.find('#callDeleteUserConf'); x.dataset.userid = button.data('userid') }) i get x.dataset is undefined Any help/advice on this would be appreciated Kind Regards Adam

Thanks for this, ill take a look.

Worked Perfectly

Actually, no. I had assumed ( what do they say about this ) that it would just remove it from the ones that contained it.

Strangely this is running the code and appears to be working correctly but when looking in the Db it is not actually removing anything $jid = 21; $stmt = $conn->prepare(" DELETE ssm_job, ssm_menu_order, ssm_equipment_order, ssm_money_order FROM ssm_job JOIN ssm_menu_order USING (job_id) JOIN ssm_equipment_order USING (job_id) JOIN ssm_money_order USING (job_id) WHERE ssm_job.job_id = ? "); $stmt->bind_param( 'i',$jid ); $stmt->execute(); // echo $stmt -> affected_rows; $stmt->close();

I thought it was the brackets but when i put the following, i get the same error $stmt = $conn->prepare(" DELETE FROM ssm_job JOIN ssm_menu_order USING (job_id) JOIN ssm_equipment_order USING (job_id) JOIN ssm_money_order USING (job_id) WHERE ssm_job.job_id = ? ");

Thanks for this, looks very tidy. I have DELETE FROM ssm_job JOIN ssm_menu_order USING job_id JOIN ssm_equipment_order USING job_id JOIN ssm_money_order USING job_id WHERE ssm_job.job_id = ? and i am getting the following error Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN ssm_menu_order USING job_id JOIN ssm_equipment_order' at line 2 in /homepages/29/d742272110/htdocs/actions/delete-whole-job-action.php:5 Stack trace: #0 /homepages/29/d742272110/htdocs/actions/delete-whole-job-action.php(5): mysqli->prepare('\n ...') #1 {main} thrown in /homepages/29/d742272110/htdocs/actions/delete-whole-job-action.php on line 5

Hi All, I am adding a button that will delete many things in the system which all have the same $job_id There is going to be 10 or so tables that need to delete * where $job_id = ? Is there a better way to do this rather than just iterating my code 10 times. I was looking at just joining but isnt one benefit of prepared statements that they can be used again and again to increase speed.

Perfect, thanks you so much for this - i was banging my head with it. I will for sure have a rethink about my formatting. Thanks Again

Hi all, i am so sorry at how bad i am at setting the title my posts. Before we get to my question, i would really appreciate it if someone could let me know how you create a table on here to show results. The following sql selects all of the items in a menu and lists them out which is great. SELECT menu_name, menu_price, menu_item_name, menu_item_category, b.menu_item_id, a.menu_id FROM ssm_menu a INNER JOIN ssm_menu_connection b on a.menu_id = b.menu_id INNER JOIN ssm_menu_items c on b.menu_item_id = c.menu_item_id WHERE a.menu_id = 1 menu_name menu_price menu_item_name menu_item_category menu_item_id menu_id Menu One 22.90 Bruschetta of Italian prosciutto, Fior di latte mo... Starter 1 1 Menu One 22.90 Crisp skin chicken breast, stuffed with sage, shal... Main 7 1 Menu One 22.90 Tagine of vegetable lightly spiced, on preserved f... Main 15 1 Menu One 22.90 Test Desert Desert 23 1 The above is the full menu and shows all menu items within the menu. When they have already made some selections, and submitted a menu, i want to display the quantities next to the name of the item. I have the following sql which is just an amendment of the above SELECT menu_name, menu_price, menu_item_name, menu_item_category, b.menu_item_id, a.menu_id, d.job_id, d.menu_item_qty FROM ssm_menu a INNER JOIN ssm_menu_connection b on a.menu_id = b.menu_id INNER JOIN ssm_menu_items c on b.menu_item_id = c.menu_item_id inner join ssm_menu_order d on c.menu_item_id = d.menu_item_id WHERE a.menu_id = 1 menu_name menu_price menu_item_name menu_item_category menu_item_id menu_id job_id menu_item_qty Menu One 22.90 Bruschetta of Italian prosciutto, Fior di latte mo... Starter 1 1 26 111 Menu One 22.90 Bruschetta of Italian prosciutto, Fior di latte mo... Starter 1 1 27 1 Menu One 22.90 Crisp skin chicken breast, stuffed with sage, shal... Main 7 1 26 222 Menu One 22.90 Tagine of vegetable lightly spiced, on preserved f... Main 15 1 26 333 Menu One 22.90 Test Desert Desert 23 1 26 444 The issue that i have here is that it is displaying some items twice because they have been selected by another user. I would like to show the whole menu but only show the qty's that relate to job_id 27. I hope this makes sense and if further structure is required, i will be happy to provide it. Kind Regards Adam

This will actually work perfectly. Thank again

So i have been having a look at this work of art and wondered if you would mind helping me take it a little further. From the selections that it makes, how would i select the row that has 100 %order and the highest %menu. If none of them have 100 %order i will do {something} if i can select the row that has 100 %order and the highest %menu i will do {something else} if nothing can be found will do {another thing}

This is absolutely perfect, thank you so much. Amazing help!!

Legend of a man