BuildMyWeb

thx jaques. there are a couple dozen status values. as we near the end, most of those 24 values will be what we are after. you would still advocate for enumerating them in a long list within the query? WHERE status IN ('NOT_STARTED', 'WORK_IN_PROGRESS', 'blah blah blah', 'blah blah blah', 'blah blah blah', 'blah blah blah', 'blah blah blah', 'blah blah blah', 'blah blah blah')

i did consider it. maybe didnt handle it well tho what i have done is assigned status codes by factors of 100, like thus: 100 - not started 200 - work in progress 300 - completed so if my boss assigned more, i could fit them in 210 - temporarily on hold 220 - cancelled i imagine you would still advocate against numerics tho so what is a better way to handle this?

yes, my status values are from a numerical array established in the application. i dont understand why this is a problem. id be happy to come back and read if you were willing to take the time to explain a bit more. or we could always take it to private messaging. i like to learn "better ways" to do things but i just understand what the problem is in this case.

yep, that was it barand. thank you for all of the help. forgot your query changed the order and i didnt reflect it in my bind params. everything seems to be good now. appreciate the help. and im going to read up some more on JOINs. i knew i was limiting what i could accomplish by not explicitly writing JOINs but didnt realize my implied JOINs were obsolete and to be avoided.

ive started reading up a bit on LEFT JOIN. i better understand jaques point now about including missing rows in the joined table. when i implement barand's query, i get no results returned when i think there should be. $query = " SELECT ua.id, ua.security_key, ua.creation, ua.last_login, ua.f_name, ua.l_name, ua.email, ua.title, ua.org_name, ua.org_size, ua.manage_num, ua.manage_direct_num, ua.phone, se.status FROM user_accts AS ua LEFT JOIN sessions AS se ON se.author_id = ua.id AND se.status < ? WHERE ( $search_filter >= ? AND $search_filter <= ? ) ORDER BY $search_filter DESC "; if( $stmt = $db_connect->prepare($query) ) { $ts_start = $obj_u_data->get_start_ts(); $ts_end = $obj_u_data->get_end_ts(); $session_done = C_SESSION_DONE; $stmt->bind_param( "iii", $ts_start, $ts_end, $session_done ); $stmt->execute(); $stmt->bind_result($author_id, $security_key, $creation_db, $last_login_ts, $f_name, $l_name, $email, $title, $org_name, $org_size, $manage_num, $manage_dir_num, $phone, $session_status); $obj_arr_status = new arr_status_types(); while ($stmt->fetch()) { $last_login = date('m/d/Y', $last_login_ts); $creation = date('m/d/Y', strtotime($creation_db)); $status_label = $obj_arr_status->format_status( $session_status ); ?> <tr> <td><?php echo $creation; ?></td> <td><?php echo $security_key; ?></td> <td><?php echo $email; ?></td> <td><?php echo $l_name; ?></td> <td><?php echo $f_name; ?></td> <td><?php echo $phone; ?></td> <td><?php echo $title; ?></td> <td><?php echo $org_name; ?></td> <td><?php echo $org_size; ?></td> <td><?php echo $manage_num; ?></td> <td><?php echo $manage_dir_num; ?></td> <td><?php echo $last_login; ?></td> <td><?php echo $status_label; ?></td> </tr> <?php } $stmt->close(); }

thanks guys. particularly barand for the example. i will review JOINs and come back to this thread.

i have two relational tables with aliases 'ua' and 'se'. most data is pulled from the ua table in this query. but, if a record exists in the se table for this user (user ID is compared in both tables to find a match) we want the value in the 'status' column of the se table. if there is no matching author ID in the se table, we will provide a default value of something like 'No Match'. im not sure of a good way to go about this. the problem that arises with my query below is that if there is no record in the se table with a matching author.id, we dont get the values from the ua table either. sql query: $query = " SELECT ua.id, ua.security_key, ua.creation, ua.last_login, ua.f_name, ua.l_name, ua.email, ua.title, ua.org_name, ua.org_size, ua.manage_num, ua.manage_direct_num, ua.phone, se.status FROM user_accts AS ua, sessions AS se WHERE (" . $search_filter . " >= ? AND " . $search_filter . " <= ?) AND (se.status < ? AND se.author_id = ua.id) ORDER BY " . $search_filter . " DESC "; if( $stmt = $db_connect->prepare($query) ) { $ts_start = $obj_u_data->get_start_ts(); $ts_end = $obj_u_data->get_end_ts(); $session_done = C_SESSION_DONE; $stmt->bind_param( "iii", $ts_start, $ts_end, $session_done ); $stmt->execute(); $stmt->bind_result($author_id, $security_key, $creation_db, $last_login_ts, $f_name, $l_name, $email, $title, $org_name, $org_size, $manage_num, $manage_dir_num, $phone, $session_status); $obj_arr_status = new arr_status_types(); while ($stmt->fetch()) { $last_login = date('m/d/Y', $last_login_ts); $creation = date('m/d/Y', strtotime($creation_db)); $status_label = $obj_arr_status->format_status( $session_status ); ?> <tr> <td><?php echo $creation; ?></td> <td><?php echo $security_key; ?></td> <td><?php echo $email; ?></td> <td><?php echo $l_name; ?></td> <td><?php echo $f_name; ?></td> <td><?php echo $phone; ?></td> <td><?php echo $title; ?></td> <td><?php echo $org_name; ?></td> <td><?php echo $org_size; ?></td> <td><?php echo $manage_num; ?></td> <td><?php echo $manage_dir_num; ?></td> <td><?php echo $last_login; ?></td> <td><?php echo $status_label; ?></td> </tr> <?php } $stmt->close(); } // close if( $stmt = $db_connect->prepare($query) )

i don't understand what youre saying you want to do or, really, what your question even is. you should paste your code attempt here, tell us what youre trying to achieve, and where its going wrong.

you should probably read up on php FUNCTIONS. when a function is declared, it is set with a number of parameters. some required, some optional. when you call the function, as you are doing in your script with mail(); , you can only call with all required parameters. you have the option to use or not use "optional" parameters. http://php.net/manual/en/functions.arguments.php try this: mail($to,$subject,$message,$headers);

In case this thread gets searched out, this is the query that worked for me. thanks to help from Jacques1 $stmt_session = $db_connect->prepare( "INSERT INTO sessions ( id, author_id, token, name ) SELECT ?, ?, ?, ? FROM sessions WHERE NOT EXISTS (SELECT author_id FROM sessions WHERE author_id=?) LIMIT 1" )

im not sure how else or what else to explain. there will be a survey class. its a multiple choice survey, in concept. an instance of this survey class can be created by a user and when it is, the properties are posted to a db. my client does not want a user to be able to post properties of more than one survey instance to the db at a time. so i have a table in the db, called sessions. (i have explained why he *shouldn't* use programming terms, but i disagree on *can't*. its his project. he's paying for it. i advise and then do what he wants.) each session in the table has a field/column named author_id. this author_id corresponds to the primary/unique field called 'id' in the user_accts table.

is this the best way to go about it?

@benanamen. oh no. thats not my issue of concern. users must log in successfully to access the application at all. the client insisted on the use of the term 'session' and that might be confusing things. when users login to their account, they can create an instance of a survey. the client wants to call this instance a 'user session'. each user (already registered and validated) can only create one survey session at a time. so if theyve already created one, it must be deleted (by the user) before they can create a subsequent one.

i suspect you are right benanamen as noted above, i have a 'sessions' table. users of the application are permitted only one 'session' at a time. so when they create a session, i am storing their user ID in the session table in the author_id column/field. so when a user attempts to create a new session, i want to verify that there exists no record in the sessions table with author_id value = user ID. i can do this easily enough in two queries, the first selecting FROM sessions WHERE author_id = user_id. if no results are returned, process the new session request and INSERT INTO the sessions table as my second query. but id like to do it all in one query, if possible. does that make more sense?

ive tried more than one approach to this problem and i cannot get any of them working. tried IF conditional with subquery and the WHERE NOT EXISTS as below. all keep triggering a sql syntax error: 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE NOT EXISTS(SELECT id FROM sessions WHERE author_id=2' at line 1' i have a 'sessions' table with a field entitled, 'author_id'. each author_id can only have one session entry at a time so if a user's author_id already exists in the 'session' table, the insert should not fire. if( $stmt_session = $db_connect->prepare("INSERT INTO sessions ( id, name ) VALUES (?, ?) WHERE NOT EXISTS(SELECT id FROM sessions WHERE author_id=?") ) { $id = 0; $author_id = $obj_user->get_id(); $name = $obj_user->get_name(); $stmt_session->bind_param('isi', $id, $name, $author_id); if( $stmt_session->execute() )