Gemini 🤖

The error was because I didn't specify a specific column in the second select query. I knew it was something simple.

Have you tried a query like this? SELECT * FROM Table1 INNER JOIN Table2 ON (Table1.id_text = Table2.id_text) GROUP BY id_product

Just when I start celebrating the fact that I'm getting the hang of multiple table queries, I stall on one for a several hours again...haha I am trying to determine if a venue has open tables on a certain date or not. Each venue can have several tables. The query below gives me an error: #1241 - Operand should contain 1 column(s) SELECT * FROM venues INNER JOIN venue_tables ON (venues.venueID = venue_tables.venueID) INNER JOIN cities ON (venues.cityID = cities.cityID) INNER JOIN states ON (venues.stateID = states.stateID) INNER JOIN price ON (venues.priceID = price.priceID) WHERE venues.cityID='74' AND venue_tables.tableID NOT IN (SELECT * FROM reservations WHERE reservationDate = '2010-11-26') GROUP BY venue_tables.tableID ORDER BY venueName ASC I simply want to return a list of venues that have an open table on a certain date. I've hard coded the query for testing purposes. Any help would be greatly appreciated. I'm sure I'm missing something simple. Thanks in advance, Twitch EDIT NOTE: I had to edit the venue_tables JOIN to be ON venueID instead of tableID still get the error though

PFMaBiSmAd, you're amazing and I'm an idiot...haha You nailed the problem. I had set up the reservation_items table a long time before I actually needed it and I had set the reservationID not only as the primary key, but to auto_increment! Clearly it doesn't need either in a one to many relationship. I never think to look at the database when a problem occurs, I always focus on the code of the page. Thank you thank you thank you! I am bookmarking this page so I can reference your other suggestions. -Twitch

Yes I put the double quotes around the query and at least I got the insert to work. Unfortunately the loop isn't. Trying to figure that out now.

Much obliged for the reply, PFMaBiSmAd. It's almost working now. Putting the double quotes made the insert work. Thank you. Unfortunately it seems to only insert the first product and quantity. If the productsField value is "1,2" and the quantitiesField is "4,3" only one row for the reservation gets inserted using the first values. So if the reservationID is "30" then I see reservation ID | productID | productQuantity 30 | 1 | 4 in the database instead of reservation ID | productID | productQuantity 30 | 1 | 4 30 | 2 | 3 Like you said, my code (now that you informed me about the single and double quotes) should work so I'm baffled again. Which isn't that uncommon...haha -Twitch

Hello all, been trying for over a day to get something to work and after searching and searching I could not find what I was looking for. I found people looking to do something similar, but the answers seemed more complicated than I would think they need to be. First off, I'm still horrible at arrays and loops so try not to laugh too hard if my code is waaaayyyy off...haha I am essentially trying to tie products and quantities to a reservation. The user has the ability to add items to the form. I'm using jquery .clone to create each new item product and quantity form fields. When the user submits the form jquery inputs the values of the cloned fields into 2 hidden text fields called "productsField" and "quantitiesField" The values of these fields look something like value="1,2,2,1,3" depending on how many products were added. What I want to do is explode both and then enter them into the database. Seems simple enough, but apparently I am not getting it. I thought something like this would work, but it is not. if (isset($_POST["reserve_button"])) { $theReservationID = $_SESSION['createdReservationID']; $items = explode (",",$_POST['productsField']); $quantities = explode (",",$_POST['quantitiesField']); // loop through array $number = count($items); for ($i=0; $i<=$number; $i++) { // store a single item number and quantity in local variables $itno = $items[$i]; $quant = $quantities[$i]; if ($items[$i] <> '') { mysql_query('INSERT INTO reservation_items (reservationID,productID,productQuantity) VALUES($theReservationID,$itno,$quant)'); } } } Any help would be greatly appreciated. Thanks in advance, Twitch

Sorry for the delay in response. Was in Houston watching my Texans blow another game...haha Only difference in your code and mine is that I wrote too much code...haha

Never mind, I fixed the looping issue and I think the update and delete is working. Will test more to be sure. You are DA MAN! Thanks again! -Twitch

it may also help to see your php code.

one thing that may be stalling the javascript is the \ you have in the <img /> tag This: <img src="loading.gif"/\> should: <img src="loading.gif"/>

I found the error. Pesky ; The first echo statement was missing it. Problem now is only checkboxes for clubs that the user has access to are showing up which is weird cause I didn't change the query. They are checked, but if the user doesn't have access that club the checkbox doesn't show up. How long you been working with php that you can rattle off code on the fly like that without seeing all of the code? I get confused and I have all the code in front of me...haha

Man just when I think I'm learning php pretty well I come across someone like you...haha I've never written code like that. Pretty awesome. I've got all the code on the page, but I'm getting a blank white page. I think the issue is with the code to create the checkboxes but I can't tell why. <?php while ($row = mysql_fetch_assoc($venues_RS)) { $checked = ($row['access']==1) ? ' checked="checked"' : ''; echo "<input type=\"checkbox\" name=\"venueID[]\" id=\"venueID_{$row['venueID']}\" value=\"{$row['venueID']}\"{$checked} />\n" echo "{$row['venueName']} {$row['venueID']}<br />\n"; } ?> By the way...thanks again for the help.

I would create a list of emails from a recordset by looping through them and when one is clicked on use jquery by using the javascript below. http://jquery.com/ and you can get a loading.gif here http://www.ajaxload.info/ <script type="text/javascript"> //jquery ajax $(function() { $(".yourTrigger").click(function() { $("#yourDisplayDiv").html("<img src='images/loading.gif' width='28' height='28' align='absmiddle'/> Loading..."); var id = $(this).attr("id");//get id of clicked var string = 'id='+ id ; $.ajax({ type: "POST", url: "details.php", data: string, cache: false, success: function(html){ $("#yourDisplayDiv").html(html); } }); return false; }); }); //end jquery ajax </script> and in the details.php file... <?php DATABASE CONNECTIONS HERE // Retrieve data from Query String $detail=$_POST["id"]; // Escape User Input to help prevent SQL Injection $detail = mysql_real_escape_string($detail); //build query $query = "SELECT * FROM your_table WHERE id = '$detail'"; //Execute query $qry_result = mysql_query($query) or die(mysql_error()); // Insert a new row in the table for each favorite returned while($row = mysql_fetch_array($qry_result)){ echo some sort of string; ?> Not sure if all this code is accurate but it should get you started. -Twitch

Sorry about that, you're right. Essentially, the query that you helped me with helps generate checkboxes of the venues on an update user page. Now I am trying to update the access based on whether the checkbox is checked or not. This code is what I use to insert the access from checkboxes on the insert user page... //cycle through the venue checkboxes and add to venue_user_access table if (isset($_POST["Send"])) { foreach($_POST as $key => $value) { if(strpos($key, 'venueID') !== false) { mysql_query("insert into venue_user_access (userID, venueID) values (" . $_SESSION['createdUserID'] . ", " . $value . ")"); } } } //end cycle through the venue checkboxes and add to venue_user_access table ...it works perfectly... ...so I thought something like this... //cycle through the venue checkboxes and add/delete to venue_user_access table if (isset($_POST["Update"])) { foreach($_POST as $key => $value) { if(strpos($key, 'venueID') !== false) { mysql_query("INSERT INTO venue_user_access (userID, venueID) VALUES (" . $_GET['userID'] . ", " . $value . ") WHERE NOT EXISTS (SELECT * FROM venue_user_access WHERE userID=". $_GET['userID'] ." AND venueID=". $value .")"); }else{ mysql_query("DELETE FROM venue_user_access WHERE userID=" . $_GET['userID'] . " AND venueID= " . $value." WHERE EXISTS (SELECT * FROM venue_user_access WHERE userID=". $_GET['userID'] ." AND venueID=". $value .")"); }//end if(strpos($key, 'venueID') !== false) } } //end cycle through the venue checkboxes and add/delete to venue_user_access table ...would work to update... I don't get an error, nothing happens.

Works like a champ my friend. You are query guru. And since you are maybe you can spot what is wrong with my insert and delete queries to update the access...haha //cycle through the venue checkboxes and add/delete to venue_user_access table if (isset($_POST["Update"])) { foreach($_POST as $key => $value) { if(strpos($key, 'venueID') !== false) { mysql_query("INSERT INTO venue_user_access (userID, venueID) VALUES (" . $_GET['userID'] . ", " . $value . ") WHERE NOT EXISTS (SELECT * FROM venue_user_access WHERE userID=". $_GET['userID'] ." AND venueID=". $value .")"); }else{ mysql_query("DELETE FROM venue_user_access WHERE userID=" . $_GET['userID'] . " AND venueID= " . $value." WHERE EXISTS (SELECT * FROM venue_user_access WHERE userID=". $_GET['userID'] ." AND venueID=". $value .")"); }//end if(strpos($key, 'venueID') !== false) } } //end cycle through the venue checkboxes and add/delete to venue_user_access table

When I first read your post I thought that was what you meant but then I guess I over thought it and decided you meant to put an access column in the table...ha ha Sorry. I will give the code a try.

Ah sorry, I wasn't thinking clearly trying to deal with a code problem I was having haha. How about: success: function(data){ $("#search_bar_hints").ajaxComplete(function(event, request, settings){ $(this).html(data); } }); }

Preliminary results are that your first query with the addition of the LEFT did the trick! THANK YOU THANK YOU THANK YOU. Man that was driving me crazy. I tried all manner of LEFT JOIN, INNER JOIN etc etc. I think I finally got to a point where I couldn't see straight...hahah Normally I would agree about putting an access column with 0 or 1 but if I did that, when an owner created a new venue all users would have to be added to the access table with that venue. I think this... SELECT * FROM `venues` as v LEFT JOIN venue_user_access as va ON v.venueID = va.venueID AND va.userID = 116 WHERE v.venueMasterID = 113 ...did the trick as far as I can tell right now. Now I can replace the hard coded numbers with post or get or session or whatever. Can't thank you enough my friend. -Twitch

I'm going on the assumption from the words "input" and "search" in your code that you're trying to do an autocomplete feature on a text field? If so, I use jqueryui library http://jqueryui.com/ and use the javascript below. <script type='text/javascript'> $(function() { $('#search').autocomplete({ source: 'update_location.php', minLength: 2 //number of characters to trigger the autocomplete }); }); </script> Then your update_location.php file would have <?php $return_arr = array(); //added to prevent sql injection $term = $_GET['term']; $hostname = "your connection"; $database = "your database name"; $username = "database username"; $password = "your password"; $term = mysql_real_escape_string($term);//prevent sql injection $conn = mysql_connect($hostname, $username, $password) or die ('Error connecting to mysql'); mysql_select_db($database); /* If connection to database, run sql statement. */ if ($conn) { $fetch = mysql_query("SELECT * FROM your_table where your_column like '%" . $term . "%'"); /* Retrieve and store in array the results of the query.*/ while ($row = mysql_fetch_array($fetch, MYSQL_ASSOC)) { // $row_array['value'] = $row['your_column']"; array_push($return_arr,$row_array); } } /* Free connection resources. */ mysql_close($conn); /* Toss back results as json encoded array. */ echo json_encode($return_arr); ?> I haven't tested this but the general idea should work. Of course you need the jqueryui library and you need to link to it on the page you have the search input on. If you weren't looking for this I apologize. I ask for help on here and I like to return the favor...haha -Twitch

mjdamato, Thank you so much for the reply. The query you provided almost works for what I need. The query does indeed show me checked checkboxes of venues that venueID 116 has access to and they are only venues that venueMasterID 113 owns, but the problem is I need a list of all the venues that venueMasterID 113 owns with only the ones that venueID 116 has access to checked. For instance, your query gives me checkboxes like so: [x] Club 1 [x] Club 2 if I delete Club 1 access by getting rid of the entry in the venue_user_access table I get: [x] Club 2 I need the checkboxes to look like: [ ] Club 1 [x] Club 2 Essentially a venue master can create venue users and give them access to the venue. One venue with many users. Generating the checkboxes for the insert page for this function works great because all I have to do is query the venues table and get all the venues with the venueMasterID of 113. The problem is generating the checkboxes for the venue user update page. Again, your reply and help is much appreciated. This has been driving me crazy. I've tried DISTINCT and GROUP BY combinations and nothing working... haha I've got to be missing something simple in the query. -Twitch

Maybe my question is too vague and that's why I haven't gotten a response. Maybe a look at my structure will help. Maybe what I'm asking can't be done or my db structure is incorrect. venue_user_access table: userID | venueID 116 | 65 116 | 66 114 | 65 etc | etc venues table: venueID | venueMasterID | venueName | other columns with venue info 65 | 113 | My Club | blah blah blah etc 66 | 113 | Club 2 | blah blah The page I'm doing this query on is a page to update the user's (in my test case userID 116) access to clubs owned by venueMasterID 113. I would like to generate the checkboxes on the update user page based on clubs owned by venueMasterID 113 and have the ones that userID 116 has access to show up as checked and the other ones unchecked. Seems like a simple doable thing, but it's been a hair puller haha. Again, the help is certainly appreciated. -Twitch

gevans, I assumed it was called 'rating' as well, but he said it really was called $rating. I wasn't even aware that you could use $ in the name, but I just tried it and mysql let me...haha

Does the query work with the data hard coded? "UPDATE main SET $rating = '5' WHERE username = '1'" Not sure what your actual data is, I just picked some random numbers.

I haven't really looked at the whole code, but is the amount field an int or varchar field? Have you tried getting rid of the " around the 0, also maybe try less than or equal in case the value goes negative like you say is sometimes does: if($newquantity <= 0){