Jump to content

BuildMyWeb

Members
 View Profile  See their activity

  • Posts

    188

  • Joined

  • Last visited

 Content Type 

Posts posted by BuildMyWeb

    IF in mysql WHERE clause

    in MySQL Help

    Posted

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

    IF in mysql WHERE clause

    in MySQL Help

    Posted

    Consider this scenario.

     

    You have the following statuses

    1 - not started

    2 - work in progress

    3 - completed.

     

    So now, anything not completed has "status < 3" and all works well with your query.

     

    You boss now tells you they are introducing new status codes

    4 - temporarily on hold.

    5 - cancelled

     

    status < 3 no longer gives you all those that are not completed.

     

    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?

    IF in mysql WHERE clause

    in MySQL Help

    Posted

     

    Besides that, the condition

    se.status < ?

    is either wrong or very unfortunate.

     

    Even if the status attribute actually happens to have a numeric type which also happens to have meaningful “ranges”, you're relying entirely on side effects of the implementation. A status by itself isn't “smaller” or “bigger” than another status. When you use nonsensical comparisons like this, you'll quickly run into trouble as soon as the implementation changes (a different type, different numeric values, a new status etc.).

     

    If you want to check for a single status, use

    se.status = ?

    If you want to check for multiple possible status, use

    se.status IN (?, ?, ...)

     

    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.

    IF in mysql WHERE clause

    in MySQL Help

    Posted

    Make sure your bind_param() has the params in the same order as the placeholders in the query

     

    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. 

    IF in mysql WHERE clause

    in MySQL Help

    Posted

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

    IF in mysql WHERE clause

    in MySQL Help

    Posted · Edited by BuildMyWeb

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

    New to php, can't get a form to email data?

    in PHP Coding Help

    Posted · Edited by BuildMyWeb

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

    INSERT if value doesnt already exist

    in MySQL Help

    Posted

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

    INSERT if value doesnt already exist

    in MySQL Help

    Posted

    Ok, thats exactly what we really needed to know. And your use of programming terms with specific meaning to us will absolutely throw us off.

     

    Ok, now just explain this more clearly:

     

    "they can create an instance of a survey"

     

    What exactly does that mean? When you say to a programmer about creating an "instance" it has a whole other meaning.

     

    * It is your job to explain to the client he cant use programming words to describe other things.

     

     

    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. 

    INSERT if value doesnt already exist

    in MySQL Help

    Posted

    SQL doesn't work like this.

     

    If you want the author_id to be unique, then add a UNIQUE constraint to that column:

    ALTER TABLE sessions ADD CONSTRAINT UNIQUE (author_id);

    This may be problematic in the context of sessions, though, because if the session isn't terminated (for whatever reason), you'll not be able to insert a new session. It might more sense to overwrite the old session or keep multiple sessions with only one marked as active.

     

     

    is this the best way to go about it? 

    INSERT if value doesnt already exist

    in MySQL Help

    Posted

    @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.

    INSERT if value doesnt already exist

    in MySQL Help

    Posted

    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?

    INSERT if value doesnt already exist

    in MySQL Help

    Posted

    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 only countdown with action to follow when countdown time reach 0

    in PHP Coding Help

    Posted · Edited by BuildMyWeb

    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
{  }
?>

    Trouble with Joining Tables in prepared select statement

    in MySQL Help

    Posted · Edited by BuildMyWeb

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

    Trouble with Joining Tables in prepared select statement

    in MySQL Help

    Posted

    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'; }

     

    backgroundColor not working

    in Javascript Help

    Posted

    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?

     

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.