sintax63

Well the issue I'm having now is with the # symbol itself. It is getting tossed up into the address bar, but my code seems to be ignoring it when I echo it out on the page.

I found the issue: $letters = ereg_replace("[^A-Za-z0-9 ]", "", $searched); Thanks everyone.

Thanks for the help everyone! I have normalized my database per AbraCadaver suggestion. I now have three like so: 'schedules' - contains two columns. One has the workshop code and the other the user ID 'workshops' - contains information about the workshop like name, description, dates, etc 'persons' - contains information about each person. (I don't think this is relevant for this query as I already have the users ID) I have spent hours on this throughout the night trying different queries and reading forums and help docs online. I'm still having issues. I believe that I only need to call two tables, not three, as I already have the ID of the person from an above call - shown below as $id. (Please note that I'm stripping off the last 4 characters from the string in my workshops.name column so it will match the abbreviated version in my schedules.courseID column) $query = "SELECT s.courseID, s.userID, w.name, w.short_title FROM schedules s LEFT JOIN workshops w ON s.courseID = substr(w.name, 0, -4) WHERE s.userID = '$id'"; This is spitting out information from my 'schedules' table, but nothing from my 'workshops' table. I also tried the below which seemed like a much simpler solution, but also came up empty. $query = "SELECT * FROM schedules, workshops WHERE schedules.courseID = substr(workshops.name, 0, -4) AND schedules.userID = '$id'"; What am I missing here? It's driving me crazy!

Good morning, everyone! I was hoping I could get a bit of help here. I'm setting up a short registration system for some workshops we have coming up here at work. Basically, for the part that matters for this issue, people can go in and choose from a list of upcoming classes they want to take. I am then storing them in my table ('classes' column) by ID's separated by a comma: 01, 14, 12, 07 So in that example above, the person registered for four workshops. Now here is where I'm having trouble (and perhaps I'm going at this all wrong). I have a summary page for each employee where they can see what classes they signed up for, the title, time, description, etc. All this information is stored in an existing table. What I need to do is create a MySQL query that will go through each of those ID's above, run them against my other table, and spit out corresponding information. Since I don't know how many workshops the person signed up for, I am not sure how to tell my query to keep looping through this comma delimited string until it's done. I know I probably need to make an array, which I imagine would look something like this: $classArray = str_getcsv($classes, ", ", "'"); I hope I'm explaining this correctly. Any help out there would be greatly appreciated! Thanks! [ I'm running a fresh install of PHP and MySQL on my server ]

Alright, Mikosika. I was already working on what I posted above before I saw Jessica's suggestion. I'm not familiar with normalization but am currently reading into it...

OK, I'm working now with something like this. I am using 7 variables for each because I don't know if it can be calculated automatically and add the number on the end via the loop or not. In any case... $partsName = explode(",", $staff_name); $staffName1 = str_replace("(", "", $partsName[0]); $staffName2 = trim(str_replace(")", "", $partsName[1])); $staffName3 = trim(str_replace(")", "", $partsName[2])); $staffName4 = trim(str_replace(")", "", $partsName[3])); $staffName5 = trim(str_replace(")", "", $partsName[4])); $staffName6 = trim(str_replace(")", "", $partsName[5])); $staffName7 = trim(str_replace(")", "", $partsName[6])); $partsID = explode(",", $staff_id); $staffID1 = str_replace("(", "", $partsID[0]); $staffID2 = trim(str_replace(")", "", $partsID[1])); $staffID3 = trim(str_replace(")", "", $partsID[2])); $staffID4 = trim(str_replace(")", "", $partsID[3])); $staffID5 = trim(str_replace(")", "", $partsID[4])); $staffID6 = trim(str_replace(")", "", $partsID[5])); $staffID7 = trim(str_replace(")", "", $partsID[6])); Now I just need to figure out how to figure out what $facultyIDx belongs to my passed through variable, and then count that position and somehow translate that count to $facultyNamex. Am I even getting close?

I have two fields in my database table that I need to sort out and match up. On is staff_id and the other is staff_name. staff_id 1958324, 5930349, 6802941, 0592854 staff_name John D. Doe, Sammy D'man, Lucy Lane, Jesse J. James What I need to be able to do, is know that the first ID goes with the first name, second ID with second name, etc... I'm passing a variable through (the staff_id number) and I need to be able to query my database and spit out the matching name. Any help out there?

I'm pulling results from two tables, which I've done dozens of times now with no issues. However on this one occasion, I am getting an odd behavior where my main table is looping the results correctly, but my joined table only shows the first results over and over again. ====================== 0001 | Title A 0002 | Title A 0003 | Title A 0004 | Title A 0005 | Title A ... and so on. I've searched, googled and even yelled at the computer. Alas, I'm stuck. SELECT s.course, s.datetime, s.id, s.status, d.name, d.title FROM syllabi s INNER JOIN schedule_desc d ON substr(s.course,0,0) = substr(d.name,0,-4) ORDER BY s.course ASC s.course has a string length of XXXX-0000 where d.name has a string length of XXXX-0000-XXX. Any thoughts?

Yeah, for some reason it was set up as a VARCHAR and I didn't notice it. After I got that working (thanks Jessica and shlumph) everything else fell into place. Thanks again!

I have a query that pulls my top 10 most clicked on entries. The code is as follows: Select * FROM clicks WHERE title!='' ORDER BY (clicks + 1000) DESC LIMIT 10 The reason I'm using (clicks + 1000) is because an entry with 4 clicks gets listed before one with 29. I'm sure there is a way around that but haven't figured that one out yet either. Now, getting back to the point of this topic, I would like to take those top 10 entries from the query above, and select one entry at random to show. SELECT c.name, c.title, c.clicks, s.name, s.title, s.description, s.synonym, s.dept FROM clicks c INNER JOIN schedule s ON substring(c.name,1,5) = substring(s.name,1,5) WHERE c.title!='' ORDER BY (RAND() * (c.clicks + 1000)) LIMIT 1 Sadly, this is pulling a random entry from all of my entries, not just one from the top 10. Any ideas? Thanks!

Thanks guys! I'll go read up on file_exists(). Cheers.

I've done some searching and digging on here but have come up empty. Maybe it isn't possible or I'm just not using the correct terminology. In any case, here is what I would like to accomplish... I want to query a database for a name (easy peasy) and then see if a file (PDF in this case) named the same name exists in a specific directory. If it does, display a link to it. If not, state the fact no file is found. So if I am querying the database for "1234-5678", it checks "/files/items" for "1234-5678.pdf". Is something like this even possible? Thanks! ------------------------------------------------------------------------------------------------- I'm running a fresh and up to date install of both PHP and MySQL

THANK YOU. THANK YOU. THANK YOU! How can I buy you a beer or a coffee for all that time (and your patience!)?

I think I may have totally just gotten it! $query = "SELECT * FROM `rubric` AS r, `schedule_desc` AS s WHERE r.code LIKE CONCAT(SUBSTR(s.name,0,4),'%') AND topic NOT LIKE '%*%' GROUP BY topic ASC"; Still listing rubric that have no matching schedule entries.

I have been messing with this all weekend and kind of got another way working, but it also has a flaw that I'm not sure can be corrected. $query="SELECT * FROM rubric WHERE title NOT LIKE '%*%' GROUP BY title ASC"; $result=mysql_query($query); while ($list = mysql_fetch_array($result)) { $letter = strtoupper(substr($list['title'],0,1)); $code = $list['code']; $title = $list['title']; include("../includes/globals.php"); if ($letter != $prev_row) { echo "<a name=\"$letter\"></a> $letter \n"; } echo "<li><a href=\"topic.php?code=$code\">$title</a></li> \n"; $prev_row = $letter; } That gives me a list and layout that I can make work, with one exception. It is listing all the results from rubric, even if nothing in the schedule table has a matching value. Is there a way to only have echo a $title if it finds a matching entry for $code in the schedule table?

AH! Well I'm not going to know what that is. See the rubric table is static but the schedule table changes every week and gets wiped out and reimported from a CSV file that is generated by another program. Perhaps I'm just out of luck on this one.

Sorry about that. I had it declared elsewhere but I went and plugged it back into your original code. I'm now getting just an empty results area (no error message and the page fully loads) The rubric_id field in my schedule table contains 0 for each entry. Is that how it should be or should they be blank / unique?

xyph - Thanks for all that. I have done as you suggested and am getting this error using this code: <?php ini_set('display_errors',1); error_reporting(-1); $q = 'SELECT rubric.id as rubric_id, rubric.title, rubric.code, schedule.id, schedule.name, schedule.short_title FROM schedule INNER JOIN rubric ON rubric.id = schedule.rubric_id ORDER BY rubric.title'; $r = $sql->query($q); // Check if query returned FALSE if( $r === FALSE ) { echo 'Query Failed'; } else { // This will hold the current rubric title we're looping through $current_rubric = FALSE; while( $row = $r->fetch_assoc() ) { // This checks if a current rubic hasn't been defined, or if a new rubric has started if( $current_rubric === FALSE || $current_rubric != $row['rubric_id'] ) { // Update the current rubric to the one in this loop $current_rubric = $row['rubric_id']; // Output the rubric name, only when we've moved on to a new one echo "<h3>{$row['title']}</h3>"; } echo "<b>{$row['short_title']}</b> <small>({$row['code']}-{$row['name']})</small><br>"; } } ?> My line 206 is: $r = $sql->query($q);

Getting closer I think. Right now I get all the headings displayed correctly but under each of them I have the same, single schedule.short_title repeated over and over. $query = "SELECT * FROM `rubric` AS r, `schedule` AS s WHERE s.name LIKE CONCAT(SUBSTR(r.code,0,3),'%')"; $result = mysql_query($query); while( $row = mysql_fetch_assoc( $result ) ) { $code = $row['code']; $title = $row['title']; $name = $row['name']; $short_title = $row['short_title']; include("../includes/globals.php"); if ($title != $prev_row) { echo "<a name=\"$title\"></a>$title< \n"; } echo "<ul> \n"; echo "<li><div id=\"listZoom2\"><a href=\"course.php?name=$shortName\">$shortTitle</a></div></li> \n"; echo "</ul> \n\n"; $prev_row = $title; }

What I'm getting now is a massive list of just the first rubric.title over and over again. <? $query = "SELECT * FROM `rubric` AS r, `schedule` AS s WHERE s.name LIKE CONCAT(SUBSTR(r.code,0,3),'%') GROUP BY s.short_title ASC"; $result = mysql_query($query); while( $row = mysql_fetch_assoc( $result ) ) { $code = $row['code']; $title = $row['title']; $name = $row['name']; $short_title = $row['short_title']; include("../includes/globals.php"); if ($title != $prev_row) { echo "<a name=\"$title\"></a> <h3>$title</h3> \n\n"; } else { echo "<div id=\"listZoom2\"><a href=\"course.php?name=$shortName\">$shortTitle</a></div>"; $prev_row = $title; } } ?>

Wow, that code worked great! I now have a huge list of all the results. I'm just now sure now how to get the rubric.title to show and group them accordingly. Thanks!

Hmmm. Still just getting an empty area where the results should be displayed. My current code: <? $query = "SELECT rubric.title, rubric.code, schedule.name, schedule.short_title FROM schedule INNER JOIN rubric ON rubric.code = substr(schedule.name, 3) ORDER BY rubric.code"; $result = mysql_query($query); $i = 0; while( $row = mysql_fetch_assoc( $result ) ) { $code = $row['code']; $title = $row['title']; $name = $row['name']; $short_title = $row['short_title']; include("../includes/globals.php"); $i++; echo "<div id=\"listZoom2\"><a href=\"course.php?name=$shortName\">$shortTitle</a></div>"; } ?> Also, where would I put the PHP to spit out the rubric.title?

Thanks, jesirose - I'll give that a try right now!

OK, I've scoured the forms on here, searched and tutorial read and even pulled out an old PHP book. Alas, I'm stumped. First off, my two tables that I'm trying to pull data from: rubric ------------------------------------- ---- code ---- | ---- title -------- ------------------------------------- ABCD | Category Name schedule ---------------------------------------------------- ------ name ------ | ------ short_title ------ ---------------------------------------------------- ABCD-123-4569 | Title Is Here Now, none of these share the same value, per say. rubric.code shares the first THREE letters from schedule.name, but that is all. What I'm trying to do is something similar to the old: A apple automobile B banana basket bench C car child crumb ... etc, etc, where the letter heading is actually the rubric.title value and then underneath it it lists all the matching short_title from schedule where the first four letters of schedule.name match the value of rubric.code - if that even makes sense. Unfortunately these are the tables I have to work with. If anyone out there has any ideas, I would be thrilled to hear them. Another thing that is driving me crazy is that this server I'm working on doesn't have error reporting turned on so I've been stuck with just a white screen to let me know something is amiss. I'm running PHP Version 5.3.3-7+squeeze8 if that helps. Thank you!

Thanks again, Fenway. And I'm sorry about not including that info in my original post.