bachx

Don't mind 'status', consider it not there, other fields are really irrelevant. What I'm trying to do? I have a given User ID (uid), for example '2'. I want to get all records that exists in both tables for that user. However User '2' might have a record in Friends but not in Friends Invitations, and vice versa, hence I used 'OR' in my original query. My original query is 100% correct, however performance-wise it's bad, because it's scan all records of Friends table in order to get to User 2, even though 'uid' is an index. Hope I was clear enough.

Explain for the query: select_typetabletypepossible_keyskeyrefrowsExtra SIMPLEfALLuid(NULL)(NULL)500- SIMPLEfireffidfidf.fid12Using where

No errors, mysql_num_rows returns 1, adding quotes changes nothing. The query in my topic works fine, however it scans all rows of the friends table in order to retrieve a single row, despite 'uid' being an index. It's definitely caused by the 'OR', as when I remove it the query executes faster, so I'm looking for a more efficient way to implement it.

Any ideas guys? It's kinda urgent

Sure thing: Friends Table: - fid (Auto-increment/Primary key) - uid (Unique index) Friends Invitations Table: - id (Auto-increment/Primary key) - fid (Index) - uid (Index) This is a simplified structure so it might not make much sense, but basically I want to retrieve a user that exists either in Friends or Friends Invitations table by the 'uid' field, both tables are connected/joined by the 'fid' field.

I have this query: SELECT f.uid, f.status FROM friends f LEFT JOIN f.friends_invitations fi ON f.fid = fi.fid WHERE (f.uid = 2 OR fi.uid = 2) The above query is looping on all 'friends' records, and ignoring the f.uid index, despite fid and uid being indexes in BOTH tables. Any ideas or suggestions?

Alright, here is something on top of my head. While not the most 'complex' code, I would like to know what's the optimal way to prepare/separate something similar in logic/display files: function student_output() { //students_info() returns an array $students_arr = students_info(); $output = '<div id="students_container">'; $output .= '<div class="items_list">'; $i = 1; //Loop through students foreach($students_arr as $key=>$value) { //Correct student names for specific cases, and assign to those ones special CSS classes switch($value['student_name']) { case 'Jim': $student_name = 'James'; $css_class = 'style_1'; break; case 'Mike': $student_name = 'Michael'; $css_class = 'style_2'; break; default: $student_name = $value['student_name']; $css_class = 'default_style'; break; } //Include student image also if the current key is similar to the 'selected_student' parameter if ($_GET['selected_student'] == $key) { $output .= '<div id="student_name_'.$i.'" class="selected '.$css_class.'">'.$student_name.'</div>'; $output .= '<div id="student_img_'.$i.'" class="selected"><img src="'.$value['student_image'].'" /></div>'; } else { $output .= '<div id="student_name_'.$i.'" class="'.$css_class.'">'.$student_name.'</div>'; } $i++; } return $output; }

MrAdam, while that works great for a basic display, my original question was on how to work with more complex ones. For example when you have to modify a variable, and maybe use an IF (or Switch) statement within each loop. So we're back to mixing logic and display again, defeating the whole purpose of separation. Now I'm starting to think there is no optimal solution for this, in that case is there really any advantage of separating the HTML, knowing that there will be a considerable amount of PHP code in there?

My issue was when the HTML code grows bigger and more complex, it's gonna be a bit of a mess having a wall of out-of-place HTML code in the middle of a PHP file. Thinking more about it though, the same mess is gonna be replicated but the other way around in an HTML file, unless there is a magical tidier way of doing it.

I currently use HTML strings within my PHP code to display output. And while it might not be best practice, I find it non-restrictive and I can easily add loops, manipulate variables, etc. within my display. However in the interest of having a cleaner code I'm thinking of separating the HTML, without having to use a Template engine like Smarty. I don't care much about replacement patterns to be honest, I don't mind some PHP code withing the HTML, however my biggest issue is loops and having to modify a variable within each loop. Say I have the following example code (Similar to what I'm using right now): function student_output() { //Retrieve students from array $students_arr = students_info(); $selected_student = $_GET['selected_student']; $output = '<div id="students_container">'; $output .= '<div class="items_list">'; $i = 1; //Loop through students foreach($students_arr as $key=>$value) { if ($selected_student == $key) { $output .= '<div id="student_name_'.$i.'" class="selected">'.$value['student_name'].'</div>'; $output .= '<div id="student_img_'.$i.'" class="selected"><img src="'.$value['student_image'].'" /></div>'; } else { $output .= '<div id="student_name_'.$i.'">'.$value['student_name'].'</div>'; } $i++; } return $output; } What's the best way to represent this in HTML cleanly without having too much PHP code within?

What advantages do sessions have over global variables? Yeah, pass the data into whatever class or function it's needed as a parameter. This is probably the best solution, though might be a bit redundant if I have lots of functions.

If I want to load some user information from the database into an object/array and share it throughout my application, what's the best approach for this? Here is what I thought of doing: - I can call a function (e.g user_info() ) that will return the user information whenever I need it , but It'll have to run a DB query each time I call it. - Load user information once from the DB, assign it into a global array/object, then call that object whenever I need it. A lot of people recommended against using global variables, but I think performance wise it's better than running a query each time. Are there any better alternatives than the above approaches?

Thanks for the reply. So if I'm looking for pure performance, MySQL is the way to go? For those that used both PDO and MySQL, is PDO noticeably slower in actual web performance? I can see the advantage of prepared statements with repeated queries, however it's somewhat situational.

Alright guys, I see people recommending prepared statements (PDO/MySQLi) and saying that they are way to go these days. However upon doing a bit more research, I've found that prepared statements, PDO in particular, is lacking in terms of performance, especially using SELECT statements. Now I'm starting a new project, which is basically a text based game with lots of queries and DB interaction, so I'm really interested in knowing what's the best approach for me. I was leaning towards PDO but I don't want it crawling my server under heavy load. I appreciate any advices or first hand experiences on this.

Bump for the questions in my last post.