Gemini 🤖

maybe try without ' "UPDATE main SET $rating = $newvotes WHERE username = $rated" or try "UPDATE main SET $rating ='".$newvotes."' WHERE username = '".$rated."'" is the database column really named $rating or is it simply rating?

Well my friends, I have been pulling my hair out for over a day and I finally had to admit I needed help. I haven't had much experience with one to many relationships and this particular query is driving me batty. Ok, I am generating some checkboxes via a table like so: <?php do { ?> <input <?php if (!(strcmp($row_venues_RS['userID'],116))) {echo "checked=\"checked\"";} ?> name="venueID_<?php echo $row_venues_RS['venueID']; ?>" id="venueID_<?php echo $row_venues_RS['venueID']; ?>" type="checkbox" value="<?php echo $row_venues_RS['venueID']; ?>" /> <?php echo $row_venues_RS['venueName']." ".$row_venues_RS['venueID']; ?><br /> <?php } while ($row_venues_RS = mysql_fetch_assoc($venues_RS)); ?> This query almost works. The proper venue (or venues) is checked, but as you can guess I get duplicates: SELECT venues.venueID, venues.venueName, venue_user_access.userID FROM venues INNER JOIN venue_user_access USING (venueID) WHERE venues.venueMasterID = %s AND venueStatus = 1 ORDER BY venueName ASC These checkboxes are on an update record page so my goal is to show all venues tied to the venueMasterID (the value is stored in a sessions variable) from the venues table and check the ones that userID 116 has access. The access table essentially has a venueID and userID field. Is this possible with a single query? I haven't written a lot of complex ones therefore I may be missing some function to make this easier. I have read up on and tried DISTINCT and GROUP BY, but never got the results I desired. Thanks in advance, Twitch

Good catch, jbrown84! I added it and that did the trick. I could then get the values I needed from the sites table. select employees.idnum, employees.lname, employees.fname, employees.location, employees.prid, sites.siteID, sites.sitename, sites.defaultCode, sites.initials, sites.dept from employees, sites where employees.location = sites.sitename and temp <> '1' and ( idnum in ($idnumstring) AND ( idnum in (select idnum from CurrentLDAP) or idnum in (select idnum from shifts where intime >= '$start' and outtime <= '$stop')) ) and salary = 'hourly' order by lname, fname"; WHOOO HOOO feels good after working on this all day! Thanks again guys this site has never disappointed me for help. Much gratitude, Twitch

I thought I'd give something like this a try: select employees.idnum, employees.lname, employees.fname, employees.location, employees.prid, sites.siteID, sites.sitename, sites.defaultCode, sites.initials, sites.dept from employees, sites It didn't break it, it just made it so that each entry was doubled or tripled. Maybe I'm on to something...haha

Can't tell you how much I appreciate the replies guys. Been at this all day. Getting the dept value from the sites table is the last piece of the puzzle. Unfortunately, I tried ngreenwood6's idea and that didn't work either. The page generates an excel file. It works with the data I had before, but when I add the second select statement, the page quits working. I've even broken the statement down to the most basic select and it still breaks the page. I know it's probably something simple, I'm just not seeing it at the moment. Thanks again, Twitch

Thanks for the reply, jbrown84. Yea I tried using JOIN on the first query but couldn't get it to work, then I noticed the second sites query and thought maybe I could do something with that but I tried various things and that didn't work either. JOINS and foreach's still confuse me, but I'm learning haha.

Hello all, Once again I turn to the experts here for some help. Been trying various versions of code to get what I want, but no luck. I'm working on a page I didn't originally create and I want to keep as much of the original code as I can. So I have this code: $sql = "select idnum, lname, fname, location from employees where temp <> '1' and ( idnum in ($idnumstring) AND ( idnum in (select idnum from CurrentLDAP) or idnum in (select idnum from shifts where intime >= '$start' and outtime <= '$stop')) ) and salary = 'hourly' order by lname, fname"; $employee = Perform_Select_Query ( $sql, Return_Configuration () ); print $employee[0][error]; $i=0; foreach ( $employee as $k => $v ) { if ( !$employee[$k][idnum] ) { break; } # build array of idnums $employees[$i] = $employee[$k][idnum]; # hash last and first names by idnum for later use $lname[ $employee[$k][idnum] ] = $employee[$k][lname]; $fname[ $employee[$k][idnum] ] = $employee[$k][fname]; $emplocation[ $employee[$k][idnum] ] = $employee[$k][location]; $i++; } # get list of all offices (sites) $sql = "select sitename from sites"; $site = Perform_Select_Query ( $sql, Return_Configuration () ); print $site[0][error]; $i=0; foreach ( $site as $k => $v ) { if ( !$site[$k][sitename] ) { break; } $sites[$i] = $site[$k][sitename]; $i++; } foreach ($employees as $person) { # new, much more efficient sql query $regular_approved=0; $regular_unapproved=0; $ot_approved=0; $ot_unapproved=0; $dot_approved=0; $dot_unapproved=0; $pto_approved=0; $pto_unapproved=0; $holiday_approved=0; $holiday_unapproved=0; $disability_approved=0; $disability_unapproved=0; $maternity_approved=0; $maternity_unapproved=0; $juryduty_approved=0; $juryduty_unapproved=0; $bereavement_approved=0; $bereavement_unapproved=0; $fmla_approved=0; $fmla_unapproved=0; $sql = "select intime, outtime, location, shifttype, approved from shifts where idnum = '$person' and intime >= '$start' and outtime <= '$stop' and outtime is not null and outtime <> 0"; $shiftInfo = Perform_Select_Query ( $sql, Return_Configuration () ); print $shiftInfo[0][error]; The code after that just calculates the different shifts/types etc. What I would like to do is also display some information from the "sites" table in my table that the user sees. The "location" column in the employee table is related to the "sitename" column in the sites table. So for instance the display data is like so and it works perfectly: # display data $myDataRows .= "<tr><td>$emplocation[$person]</td><td>$person</td><td>$lname[$person]</td><td>$fname[$person]</td><td>Dept.</td><td>BERE</td><td>$bereavement_approved</td></tr>" ."<tr><td>$emplocation[$person]</td><td>$person</td><td>$lname[$person]</td><td>$fname[$person]</td><td>Dept.</td><td>DISAB</td><td>$disability_approved</td></tr>" ."<tr><td>$emplocation[$person]</td><td>$person</td><td>$lname[$person]</td><td>$fname[$person]</td><td>Dept.</td><td>DOUBLE</td><td>$dot_approved</td></tr>" ."<tr><td>$emplocation[$person]</td><td>$person</td><td>$lname[$person]</td><td>$fname[$person]</td><td>Dept.</td><td>FIXSAL</td><td>$ot_approved</td></tr>" ."<tr><td>$emplocation[$person]</td><td>$person</td><td>$lname[$person]</td><td>$fname[$person]</td><td>Dept.</td><td>HOLI</td><td>$holiday_approved</td></tr>" ."<tr><td>$emplocation[$person]</td><td>$person</td><td>$lname[$person]</td><td>$fname[$person]</td><td>Dept.</td><td>HOUR</td><td>$regular_approved</td></tr>" ."<tr><td>$emplocation[$person]</td><td>$person</td><td>$lname[$person]</td><td>$fname[$person]</td><td>Dept.</td><td>JURY</td><td>$juryduty_approved</td></tr>" ."<tr><td>$emplocation[$person]</td><td>$person</td><td>$lname[$person]</td><td>$fname[$person]</td><td>Dept.</td><td>OVER</td><td>$ot_approved</td></tr>" ."<tr><td>$emplocation[$person]</td><td>$person</td><td>$lname[$person]</td><td>$fname[$person]</td><td>Dept.</td><td>VACN</td><td>$pto_approved</td></tr>"; In the displayed data I need "Dept." to actually be the dept column from the sites table based on $emplocation[$person] value. This value is the location column from the employee. Hopefully I explained it correctly cause I think I'm confused...haha I understand if this is too much to ask with all that code, but I thought I'd give it a try before I pull anymore hair out. Thanks in advance, Twitch

Shino, you are DA MAN! Worked perfectly. Can't thank you enough for helping me. That was the last query I needed to finish this little project I'm working on. Thanks a ton! Twitch

shino, Thank you so much for your quick reply. It worked like a champ, but then I realized I forgot that I also needed to look at the current_employees table to make sure that only current employees are shown, not all that have been employed in the past. Basically the employee idnum's stored in the 'employees' table are former and current. The 'current_employees' table only has one column that contains idnum's of current employees. So how do I add a check to this table to the current code you posted so only current employees show up. The common column is 'idnum' of course. I tried to add it myself with horrendous results...haha If it's too much to ask, I understand cause you've helped me out a bunch already with the code you did supply. Thanks in advance, Twitch

Hello PHP patrons, I've been racking my brain over the last few days trying to do something and I think I'm close but I need help getting the finishing touch. I have 3 tables table 1: employees table 2: shifts table 3: projects Essentially what I'm looking to do is simply show the employees who are clocked in and do not currently have an open project. The code below works for the clocked in part of it. If I clock an employee out, they don't show up anymore, but they show up when clocked in whether they have an open project of not. I'm suspecting that the project status part is looking at all projects and not just the last one. That's the part I'm having trouble with. The project table has a projectID field as the key. So basically the highest projectID per employee is the last project record for that employee. How do I make this script only look at the last project per employee? Any help is greatly appreciated. My head is about to explode...haha Thanks in advance, Twitch $query = "select *, MAX(fname), MAX(projStatus) from (employees left join shifts on employees.idnum = shifts.idnum) left join projects on shifts.idnum = projects.idnum where shifts.intime > shifts.outtime && projects.projStatus = 'Closed' GROUP BY fname"; $who = mysql_query($query) or die(mysql_error()); while($row = mysql_fetch_array($who)){ echo $row['MAX(fname)']. " - ".$row['lname']; echo "<br />"; }

Well, my friends I started from scratch and have an almost working script. <?php // Make a MySQL Connection mysql_connect("connection", "username", "pword") or die(mysql_error()); mysql_select_db("dbname") or die(mysql_error()); $nextline = ''; $fp = fopen('test.txt','r'); while (!feof($fp)) { $mystuff = array(); $nextline = fgets($fp); $mystuff = split(',',$nextline); $casenumber = mysql_real_escape_string(trim($mystuff[0])); $casename = mysql_real_escape_string(trim($mystuff[1])); mysql_query("REPLACE INTO case_numbers (casenumber, casename) VALUES('$casenumber', '$casename' ) ") or die(mysql_error()); } echo '<p>done!'; ?> The text file will be updated with new case numbers and names. When I added a new entry to the bottom of the text file and ran the above script, the new entry was added to the database. However, if I delete the last entry, the above script does not delete the entry from the database. There are only the two fields in the table, casenumber and casename with casenumber set to unique. I'm sure it's just a little tweak to the query to see if one is missing and if so delete it. Any help would be much appreciated. Learned a lot so far doing this script. Thanks in advance.

Thanks so much for the replies guys. This site never disappoints. I tried the code above and I get "Query was emptyAll rows imported successfully!" I tried to echo $result; but get nothing. Evidently it's not grabbing the data from the file. I know I must be missing something simple. Maybe when my coffee kicks in it will be apparent...haha

I have a text file with rows that look like below: 123245,Name of Case 263746,Another Name of Case and so on..... My goal is to have a table called case_numbers update each night from this text file. I'm not worried about the automated part yet, I can't even get the data to insert yet...ha ha I basically want the data in the table replaced with the data in the text file as the text file will have new case numbers added often. After looking and looking on the net, I have this code: <?php mysql_connect ("localhost","username","password"); mysql_select_db ("database_name"); // Path to the data file $path="test.txt"; // Get the site of the file $filesize=filesize($path); // Open file, read its data and close it $filenum=fopen($path,"r"); $arrlen = count($filenum); fclose($filenum); // Listing the array inside a loop, adding it to the database and echo about it. for($i=0; $i<$arrlen ; $i++){ $bits=explode(",",$filenum[$i]); $sql_insert = mysql_query("REPLACE INTO case_numbers (casenumber,casename) VALUES ('$bits[0]','$bits[1]'"); $result = mysql_query ($sql_insert); // reports an error if insert fails. if (!$result){ echo mysql_error();} }else{ // All done, let's go home! echo "All rows imported successfully!"; } ?> Of course I put my actual connection settings for the connections. When I run it I don't get an error, but the data in the table isn't replaced. Nothing happens. Any idea where I'm going wrong. Thanks in advance, Twitch Made an edit cause I noticed my explode statement was missing the "," still doesn't work..ha ha

Ah ok, awesome, I'll give this a whirl. Once again this site doesn't disappoint. Hopefully, my skills will be good enough soon to contribute more. Thanks again my friend.

Thanks for the quick reply laffin, yes they are unique, but I'm not sure if I follow what you're saying. Course it could be that I am just now getting around to having my coffee. I'm definitely like the guy in the commercial who can't function till he's had his coffee...ha ha

I was asked by my employer to come up with a way to verify a text field using a list of account numbers. The list of numbers are in a comma delimited text file that I want to create a table from. Each time the text file is generated, it has a few more account numbers, so I figure the easy way is the drop the table and create a new one from the text file. I've found many scripts that create a table from a text file, or have all this confusing code to update a table but I haven't found the right combination I'm looking for. Thought I'd find millions of examples doing exactly what I want, but they are either overkill or not quite what I need. Basically a text file called "numbers.txt" with lines of text like below 123122,NameOfSomething 234544,NameOfSomethingElse Just two columns is all the table needs. I finally gave up and came here because in the past when I've asked a question here, someone brilliant has always had the perfect (and usually simplest) code example as my php skills are decent, but still have some learning to do...haha Thanks in advance, Twitch

Well guys in the end, my original query was correct, but I had the session variable code in the wrong place. I noticed that sometimes the simple query without the userID would suddenly stop working as well. I deleted the session variable section of code and it updated the projStatus column to "Closed" as it should, I'd add back the variable code and it would stop. I need that code so I moved it around and voilá, it worked at the top of the page. As I've learned, that's where session variable code should be in the first place...ha ha 2 days of hair pulling may require a trip to the store for some 1554 by New Belgium to celebrate. :-) Thanks again for the quick replies, this site will be added to my daily reading. Very helpful. -Twitch

Wow thanks for the amazingly quick replies. mikesta707, I have populated a couple of projects with different users so I could make sure it's not closing all of them. And indeed, it is...ha ha I'm going to use the error trigger that mrMarcus suggested and see what it tells me. I'll keep you guys informed and again, can't thank enough for the quick replies. -Twitch

Ok, I'm just getting back into learning php and I'm trying to simply update one field based on the userid in a table when a user navigates to a certain page. Basically without a a form. I've been at it for 2 days and it seems like it should be a simple no brainer, but it's giving me fits. I'm search and haven't really found a situation like mine so I gave in and decided to ask. When the page loads, the user id is grabbed from a session variable: $userid = $session['USERID']; I have a mysql_query that as of now updates the project status field to "Closed" if it is "Open". mysql_query("UPDATE projects SET projStatus = 'Closed' WHERE projStatus ='Open'"); That query works but closes every "Open" project in the table. When I try to add the userID like so: mysql_query("UPDATE projects SET projStatus = 'Closed' WHERE userID = $userid AND projStatus ='Open'"); Nothing updates at all. I can echo the $userid and it shows up correctly so the session variable is the currently logged in user. Seems like my query including the userID should work as is, no? Any help would be much appreciated. Thanks in advance, Twitch

Thank you, thank you, thank you gevans. I've only posted a couple of questions on this forum since joining, but each time everyone has been cool and quick to answer. Didn't realize I didn't need the quotes anymore on "The file" and "has been uploaded." text. Since I'm a novice to PHP can't offer you help there, but if you have a Mac question on all over that...ha ha Much gratitude, Twitch

Whoops sorry, my coffee hasn't kicked in yet...ha ha. That statement echos just fine, I meant I'm having trouble echo'ing it in a table like the error statement. Sorry about the confusion.

This one should be a layup for anyone who isn't a PHP novice like myself. I can successfully echo the following: echo '<br>'; echo '<br>'; echo '<table align="center" width="400" border="0">'; echo '<tr>'; echo '<td>There was an error uploading the file, please try again!</td>'; echo '</tr>'; echo '</table>'; However, I get a little confused on trying to echo this statement: echo "The file ". basename( $_FILES['uploadedfile']['name']). " has been uploaded"; Because of the various quotes and apostrophes. Any help would be greatly appreciated. Thanks in advance, Twitch

Whoops I forgot to post the code. <?php $b = time(); $hour = date("g",$b); $m = date("A", $b); $d = date("D", $b); echo $hour; echo $m; echo $d; if ($m == "AM") { if ($hour == 12) { echo "Good Evening!"; } elseif ($hour < 4) { echo "Good Evening!"; } elseif ($hour > 3) { echo "Good Morning!"; } } elseif ($m == "PM") { if ($hour == 12) { echo "Good Afternoon!"; } elseif ($hour < 7) { echo "Good Afternoon!"; } elseif ($hour > 6) { echo "Good Evening!"; } } ?>

Hey guys I'm hoping someone can help me with making this script get the user's local computer time instead of the server time. I know php can't do it and I know javascript can, but I'm not sure how to do it with this script. I had another script I was working on that never quite worked, but this one does what I need it to do, but with the server time instead of the local time. Thanks in advance, Twitch

I appreciate all the help, but nothing seems to work for what I'm needing. I may just scrap the idea. Again, appreciate your time. -Twitch