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() )

php is server-side. meaning it will not update the time display on your page unless you refresh the page. if you want a dynamic timer that ticks down in your browser, you will need to use a client-side solution like javascript. i suck at js so not sure about this but you might be able to trigger your php script via AJAX and have the page update on time intervals rather than user refresh. <?php // Define your target date here $targetYear = 2007; $targetMonth = 9; $targetDay = 10; $targetHour = 12; $targetMinute= 00; $targetSecond= 00; // End target date definition // Define date format $dateFormat = "Y-m-d H:i:s"; $targetDate = mktime($targetHour,$targetMinute,$targetSecond,$targetMonth,$targetDay,$targetYear); $actualDate = time(); $secondsDiff = $targetDate - $actualDate; $remainingDay = floor($secondsDiff/60/60/24); $remainingHour = floor(($secondsDiff-($remainingDay*60*60*24))/60/60); $remainingMinutes = floor(($secondsDiff-($remainingDay*60*60*24)-($remainingHour*60*60))/60); $remainingSeconds = floor(($secondsDiff-($remainingDay*60*60*24)-($remainingHour*60*60))-($remainingMinutes*60)); $targetDateDisplay = date($dateFormat,$targetDate); $actualDateDisplay = date($dateFormat,$actualDate); // not done if( $secondsDiff > 0 ) { echo "$remainingDay days, $remainingHour hours, $remainingMinutes minutes, $remainingSeconds seconds"; } // done else { } ?>

it would help if you show both scripts. do you catch the value from your url in your handler script? $id_to_edit = $_GET['edit'];

thank you mac. apparently i needed a back-to-basics wakeup. i had a collation conflict. the error message of course told me that right away and was an easy fix for something i spent hours poring over.

I had this script running well when the $stmt query was a select from a single table. The user acts table. When I edited ONLY the SELECT query in $stmt, we have the problem. so this works fine for me: $stmt = $db_connect->prepare("SELECT id, status, level, creation, f_name, l_name, pw, title, org_name, org_size, manage_num, manage_direct_num, phone FROM user_accts WHERE email=?"); if( $stmt ) { $stmt->bind_param("s", $login_email); $stmt->execute(); $stmt->bind_result($id, $status, $level, $creation, $f_name, $l_name, $pw_db, $title, $org_name, $org_size, $manage_num, $manage_direct_num, $phone); while ( $stmt->fetch() ) { // changes on every iteration to reflect the current row } $stmt->close(); }

We are making the db connection. $dbconnect->errno return of zero.

Notice: Trying to get property of non-object in /home/scripts/handler_login.php on line 41 line 41: { echo $stmt->error; }

I have two tables: user_accts AND security_keys im trying to run a prepared select query that gets several columns from the user-accts table where the email address provided in a login form is equal to the email address in a row of my table. getting all the user data for the account logging in. the second select would get a column value from the security_keys table where the value of security_key is equal in both tables. the following is echoing 'problem'. $stmt = $db_connect->prepare("SELECT ua.id, ua.status, ua.level, ua.creation, ua.f_name, ua.l_name, ua.pw, ua.title, ua.org_name, ua.org_size, ua.manage_num, ua.manage_direct_num, ua.phone, ua.security_key, sk.expiration FROM user_accts AS ua, security_keys AS sk WHERE ua.email = ? AND sk.security_key = ua.security_key"); if( $stmt ) { $stmt->bind_param("s", $login_email); $stmt->execute(); $stmt->bind_result($id, $status, $level, $creation, $f_name, $l_name, $pw_db, $title, $org_name, $org_size, $manage_num, $manage_direct_num, $phone, $security_key, $expiration ); while ( $stmt->fetch() ) { // changes on every iteration to reflect the current row } $stmt->close(); } else { echo'problem'; }

dzuh! thank you scoot i was calling it prior to the form. i suck with js.

javascript: function highlight_field( field_name ) { alert(field_name); document.getElementsByName(field_name)[0].style.backgroundColor = 'yellow'; } HTML form: <form action='http://buildmyweb.org/testing/mfs/scripts/handler_register.php' method='post'> <input class='req' type='text' placeholder='First Name' name="reg_f_name" /> <input class='req' type='text' placeholder='Last Name' name="reg_l_name" /> ... <input type='submit' name='submit_button' value='Register' id='reg_submit' /> </form> when i load the page in question, the ALERT(); is triggering and displaying the correct value, 'reg_f_name'. yet the background color of that field is not changing. what am i doing wrong?

thank you guys. i had not received emails on this topic. thought no one replied i have found what Barand foresaw, querying results across regions is a PIA. back to one table!