Jump to content

In php and sql how can i use multiple tables to populate a form for updating use

ellegua220

By ellegua220
in PHP Coding Help

 Share

Recommended Posts

ellegua220 Newbie

ellegua220

Posted
Posted

In php and sql how can i use multiple tables to populate a form for updating user information?

Here is a simplified table structure:

 

user

userid PK

first_name

last_name

 

parent

parent_id PK

parent_first_name

parent_last name

 

parent_user

parent_user_id PK

parent_id FK

userid FK

 

Here is what I have tried. I need to use the new table parent and parent_user to populate parent_first_name and parent_last_name. I think i need to JOIN the info in the select statement:

 

Here is the select statement I was trying to use but its not working:

$query = "SELECT user.userid, user.username, user.email, user.first_name, user.last_name, user.password, user.phone, user.street_address, user.other_address, city, state, zipcode, type, school, school_district, DATE_ADD(user.date_registered, INTERVAL $time_diff HOUR),user.double_gui,user.status_id,use… parent.parent_first_name from user, parent_user, parent where user.userid=$userid and parent_user.userid=$userid and parent.parent_id=$userid";

 

I also tried this:

    $query = "SELECT userid, username, email, first_name, last_name, password, phone, street_address, other_address, city, state, zipcode, type, school, school_district, DATE_ADD(date_registered, INTERVAL $time_diff HOUR),double_gui,status_id,notes FROM user JOIN parent_user USING(userid) JOIN parent USING(parent_id) where userid=$userid;

 

 

 

this is the original working code that just uses the user table:

 

query = "SELECT userid, username, email, first_name, last_name, password, phone, street_address, other_address, city, state, zipcode, type, school, school_district, DATE_ADD(date_registered, INTERVAL $time_diff HOUR),double_gui,status_id,notes from user where userid=$userid";

$result = mysql_query($query, $mysql_link);

if((!result) || (! mysql_num_rows($result)))

{

die("<p class=highlight>error getting details of user userid=$userid from db</p>");

}

 

if ($row = mysql_fetch_row($result))

{

$userid = $row[0];

$username = $row[1];

$email = $row[2];

$first_name = $row[3];

$last_name = $row[4];

//$parent_last_name = $row[5];

$password = $row[5]; // no it didn't print "<tr><td> will this print next to the password??????</td></tr>";

$phone = $row[6];

$street_address = $row[7];

$other_address = $row[8];

$city = $row[9];

$state = $row[10];

$zipcode = $row[11];

$type = $row[12];

$school = $row[13];

$school_district = $row[14];

$date_registered = $row[15];

$double_gui = $row[16];

$status_id = $row[17];

$notes = $row[18];

if ($type == "S")

{

$student_selected = "selected";

}

elseif ($type == "T")

{

$tutor_selected = "selected";

}

elseif ($type == "A")

{

$admin_selected = "selected";

}

elseif ($type == "K")

{

$tech_selected = "selected";

}

}

}

print "<form>";

print "<table>";

if ($action == "edit")

{

print "<input type=hidden name=action value=update>";

print "<input type=hidden name=userid value='$userid'>";

}

else

{

print "<input type=hidden name=action value=insert>";

}

 

// action is not edit then has to be add mp

print "<tr><td>Username:</td><td><input type=text maxlength=40 name=username value=\"$username\"> (REQUIRED)</td><tr>";

print "<tr><td>Status:</td><td><select name=status_id><option value=\"\"></option>";

Link to comment
Share on other sites
btherl Member

btherl

Posted
Posted

There's a piece missing from the middle of your first query: "use… parent.parent_first_name"

 

Please change your query code to:

 

$result = mysql_query($query, $mysql_link) or die("Query failed: $query\n" . mysql_last_error());

 

And post the error message from the query here.

 

BTW it is better to use JOIN instead of the commas to combine tables.

Link to comment
Share on other sites
MadLittleMods Newbie

MadLittleMods

Posted
Posted

Okay if they are three different tables then just do this: (assuming all your info is in one table) if in three tables post back...

 

 

Hope you get the concept..

 

// This finds all rows that have the userid as the $userid
$search_for_rows = mysql_query("SELECT * FROM user WHERE userid = $userid');

while($row = mysql_fetch_array($search_for_rows))
{
     // here is an example not sure how to select columns with a space, so just put a _ where you have a space in the db and here...
     $userid = $row[userid_PK];
}

 

Link to comment
Share on other sites
ellegua220 Newbie

ellegua220

Posted
  • Author
Posted

    $query = "SELECT userid, username, email, first_name, last_name, password, phone, street_address, other_address, city, state, zipcode, type, school, school_district, DATE_ADD(date_registered, INTERVAL $time_diff HOUR),double_gui,status_id,notes FROM user JOIN parent_user USING(userid) JOIN parent USING(parent_id) where userid=$userid;

 

this is the select statement that I am using. 

pthberl: I changed the result statment to:

 

$result = mysql_query($query, $mysql_link) or die("Query failed: $query\n" . mysql_last_error());

I am still just getting a blank white screen.  no error message.

 

I am using 3 tables: user, parent_user, parent.  I used joins I think so at least!  here:

 

 

and no i didn't understand that concept madlittlemods...

FROM user JOIN parent_user USING(userid) JOIN parent USING(parent_id) where userid=$userid;

 

Link to comment
Share on other sites
ellegua220 Newbie

ellegua220

Posted
  • Author
Posted

oldmatt what does the u do after user:

user u

 

btherl  the error display shows this before the edit button:

Notice: Undefined index: action in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 13

 

Notice: Undefined index: userid in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 14

 

Notice: Undefined index: search in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 15

 

Notice: Undefined index: status_search in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 16

 

Notice: Undefined index: status_not in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 21

 

this is with the select statment commented out so this is just listing of all the users. I haven't even clicked on the edit button

when i click on the edit button i get these errors.

 

Notice: Undefined index: search in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 15

 

Notice: Undefined index: status_search in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 16

 

Notice: Undefined index: status_not in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 21

string(2) "94"

Notice: Undefined variable: query in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 381

 

Notice: Undefined variable: query in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 381

 

Fatal error: Call to undefined function mysql_last_error() in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 381

 

here is code for the whole file. I am so lost.  thanks for your help!: 

<html>
<head>
<title>User Admin</title>
<link rel="STYLESHEET" href="../style.css" type="text/css">
</head>
<body>
<center>
<h1>User Admin</h1>
</center>
<script language="php">
ini_set('display_errors', 1);
include("../db.php");
$action = $_GET['action'];
$userid = $_GET['userid'];
$search = $_GET['search'];
$status_search = $_GET['status_search'];
if (preg_match('/[^\d]/', $status_search) > 0)
{
    die("invalid status search id");
}
$status_not = $_GET['status_not'];
if (preg_match('/[^\d]/', $status_not) > 0)
{
    die("invalid status search id");
}

if ($action == "delete")
{
    if ($userid == "")
    {
        die("<p class=highlight>no userid specified</p>");
    }
    if (preg_match('/[^\d]/', $userid) > 0)
    {
        die("<p class=highlight>invalid userid</p>");
    }
     
    $query = "DELETE FROM user WHERE userid=$userid";
    $result = mysql_query($query, $mysql_link) or die(mysql_error());
    $query = "DELETE FROM student_session WHERE userid=$userid";
    $result = mysql_query($query, $mysql_link) or die(mysql_error());
    $query = "DELETE FROM session_login WHERE userid=$userid";
    $result = mysql_query($query, $mysql_link) or die(mysql_error());

//below added by mark palmer to delete information from the new parent and parent_user tables when a user, future session and logins are deleted above.

/*$query = "DELETE FROM parent_user WHERE userid=$userid";  AT THE SAME TIME OR BEFORE IT NEEDS TO PULL OUT ALL OF THE PARENT_ID WHERE USERID=$USERID SO IT CAN DELETE FROM PARENT ID WHERE PARENTID=$PARENTID
    $result = mysql_query($query, $mysql_link) or die(mysql_error());
    $query = "DELETE FROM student_session WHERE userid=$userid";
    $result = mysql_query($query, $mysql_link) or die(mysql_error());
    $query = "DELETE FROM WHERE userid=$userid";
    $result = mysql_query($query, $mysql_link) or die(mysql_error());*/

    include("updatepasswd.php");
    include("updateconfig.php");
    print "<p class=highlight>User deleted</p>";
    print "<meta http-equiv='Refresh' content='2; url=useradmin.php'>";








}
elseif ($action == "update" || $action == "insert")
{
    // error checking preg_match('/[\047\042],varible') searches for single and double quotes  
    if (preg_match('/[^\d]/', $userid) > 0)
    {
        die("<p class=highlight>invalid userid</p>");
    }
    $username = $_GET['username'];
    if (preg_match('/[^\w\.@ ]/', $username) > 0)
    {
        die("<p class=highlight>invalid username: must only contain letters, digits, spaces, dots,  underscores and @</p>");
    }
    $email = $_GET['email'];
    if ($email != "")
    {
        if (! (preg_match('/@/', $email)))
        {
            die("<p class=highlight>invalid email address</p>");
        }
        if (! (preg_match('/\./', $email)))
        {
            die("<p class=highlight>invalid email address</p>");
        }
        if (preg_match('/[ \047\042\|<>]/', $email) > 0)
        {
            die("<p class=highlight>invalid email address</p>");
        }
    }

    $first_name = $_GET['first_name'];
    if (preg_match('/[\047\042]/', $first_name) > 0)
    {
        die("<p class=highlight>invalid first name</p>");
    }



    $last_name = $_GET['last_name'];
    if (preg_match('/[\047\042]/', $last_name) > 0)
    {
        die("<p class=highlight>invalid last name</p>");
    }


//next group of lines below added by Mark Palmer
// working
$parent_first_name=$_GET['parent_first_name'];
//var_dump($parent_first_name);

if (preg_match('/[\047\042]/', $parent_first_name) > 0)
    {
        die("<p class=highlight>invalid parent last name</p>");
    }

$parent_last_name=$_GET['parent_last_name'];
//var_dump($parent_last_name);

    if (preg_match('/[\047\042]/', $parent_last_name) > 0)
    {
        die("<p class=highlight>invalid parent last name</p>");
    } 

//var_dump($parent_first_name);
//var_dump($parent_last_name);
//print("<tr><td>next</td></tr>");

// previous lines added by mark pamer





    $password = $_GET['password'];
    if (preg_match('/[\047\042]/', $password) > 0)
    {
        die("<p class=highlight>invalid password</p>");
    }
    $phone = $_GET['phone'];
    if (preg_match('/[^ \d\.\/\(\)\+\-]/', $phone) > 0)
    {
        die("<p class=highlight>invalid phone number</p>");
    }
    $street_address = $_GET['street_address'];
    if (preg_match('/[\047\042]/', $street_address) > 0)
    {
        die("<p class=highlight>invalid street_address</p>");
    }
    $other_address = $_GET['other_address'];
    if (preg_match('/[\047\042]/', $other_address) > 0)
    {
        die("<p class=highlight>invalid other_address</p>");
    }
    $city = $_GET['city'];
    if (preg_match('/[\047\042]/', $city) > 0)
    {
        die("<p class=highlight>invalid city</p>");
    }
    $state = $_GET['state'];
    if (preg_match('/[^a-zA-Z]/', $state) > 0)
    {
        die("<p class=highlight>invalid state</p>");
    }
    $zipcode = $_GET['zipcode'];
    if (preg_match('/[\047\042]/', $zipcode) > 0)
    {
        die("<p class=highlight>invalid zipcode</p>");
    }
    $type = $_GET['type'];
    if ($type != "T" && $type != "A" && $type != "S" && $type != "K")
    {
        die("<p class=highlight>invalid type</p>");
    }
    $school = $_GET['school'];
    if (preg_match('/[\047\042]/', $school) > 0)
    {
        die("<p class=highlight>invalid school</p>");
    }
    $school_district = $_GET['school_district'];
    if (preg_match('/[\047\042]/', $school_district) > 0)
    {
        die("<p class=highlight>invalid school_district</p>");
    }
    $double_gui = $_GET['double_gui'];
    if ($double_gui != "Y")
    {
        $double_gui = "N";
    }
    $notes = $_GET['notes'];
    $status_id = $_GET['status_id'];






    if (preg_match('/[^0-9]/', $status_id) > 0)
    {
        die("<p class=highlight>invalid status</p>");
    }
    if ($status_id == "")
    {
        $status_id = "null";
    }

    if ($email == "")
    {
        die("<p class=highlight>email cannot be blank</p>");
    }
    if ($username == "")
    {
        die("<p class=highlight>username cannot be blank</p>");
    }

    if ($first_name == "")
    {
        die("<p class=highlight>first name cannot be blank</p>");
    }
    if ($last_name == "")
    {
        die("<p class=highlight>last name cannot be blank</p>");
    }
    if ($street_address == "")
    {
        die("<p class=highlight>street_address cannot be blank</p>");
    }
    if ($city == "")
    {
        die("<p class=highlight>city cannot be blank</p>");
    }
    if ($state == "")
    {
        die("<p class=highlight>state cannot be blank</p>");
    }
    if ($zipcode == "")
    {
        die("<p class=highlight>zipcode cannot be blank</p>");
    }
    if ($password == "")
    {
        die("<p class=highlight>password cannot be blank</p>");
    }
    if ($type == "")
    {
        die("<p class=highlight>type cannot be blank</p>");
    }
    
    if ($action == "insert")
    {
        // adding a new user
        if ($username == "")
        {
            die("<p class=highlight>username cannot be blank</p>");
        }
        if ($company_id == "")
        {
            $company_id = "null";
        }
     // david is using the sprintf to do some error checking.  It is inserting all of the fields into the table user.  it uses and string arg %s to get the esc checked field $notes   
        $query = sprintf("insert into user values(null, '$username', '$email', '$first_name', '$last_name', '$password', '$phone', '$street_address', '$other_address', '$city', '$state', '$zipcode', '$type', '$school', '$school_district', current_timestamp, '$double_gui', $status_id, '%s')",mysql_real_escape_string($notes, $mysql_link));
        mysql_query($query, $mysql_link) or die(mysql_error());
     $user_id= mysql_insert_id();

// var_dump($query);

// added by mark palmer inserts the null (for the pk) parents first and last name int the parent table  
     $query2 = sprintf("insert into parent values(null,'$parent_first_name','$parent_last_name')");

//var_dump($query2);

mysql_query($query2,$mysql_link) or die(mysql_error());


	// next line gets the parent id automatically created by after the insert into parents

$parent_id= mysql_insert_id();	
    var_dump($parent_id);	
var_dump($user_id);
$query3= sprintf("insert into parent_user values(null, '$parent_id','$user_id')");
var_dump($query3);

mysql_query($query3,$mysql_link) or die(mysql_error());


        if ($status_id != 'null' && $status_id != '')
        {
            $uid = mysql_insert_id($mysql_link);
            $changed_by = $_SERVER['REMOTE_USER'];
            $changed_by_userid = 'null';
            if ($changed_by != "")
            {
                $query = "select userid from user where username='$changed_by'";
                $result = mysql_query($query, $mysql_link) or die(mysql_error());
                if ($result)
                {
                    if ($row = mysql_fetch_row($result)) 
                    {
                        $changed_by_userid = $row[0];
                    }
                }
            }
            $query = "insert into user_status values(null, $status_id, $uid, current_timestamp, $changed_by_userid)";
            mysql_query($query, $mysql_link) or die(mysql_error());
        }
        print "<p class=highlight>User added</p>";
        print "<a href=\"useradmin.php?action=add\">Add More Users</a><br>";
        print "<a href=\"useradmin.php\">Back to User List</a><br>";
    }
    else
    {
        if ($userid == "")
        {
            die("<p class=highlight>no userid specified</p>");
        }

        // get old status so we can see if it changed
        $query = "select status_id from user where userid=$userid";
        $result = mysql_query($query, $mysql_link) or die(mysql_error());
        if((!result) || (! mysql_num_rows($result)))
        {
            die("<p class=highlight>error getting status of user userid=$userid from db</p>");
        }

        if ($row = mysql_fetch_row($result)) 
        {
            $old_status = $row[0];
        }

        $query = sprintf("UPDATE user set username='$username', email='$email', first_name='$first_name', last_name='$last_name', password='$password', phone='$phone', street_address='street_address', other_address='$other_address', city='$city', state='$state', zipcode='$zipcode', type='$type', school='$school', school_district='$school_district',double_gui='$double_gui',status_id=$status_id,notes='%s' where userid=$userid",
            mysql_real_escape_string($notes, $mysql_link));;
        ;
        mysql_query($query, $mysql_link) or die(mysql_error());
        if ($status_id != 'null' && $status_id != '' && $status_id != $old_status)
        {
            $changed_by = $_SERVER['REMOTE_USER'];
            $changed_by_userid = 'null';
            if ($changed_by != "")
            {
                $query = "select userid from user where username='$changed_by'";
                $result = mysql_query($query, $mysql_link) or die(mysql_error());
                if ($result)
                {
                    if ($row = mysql_fetch_row($result)) 
                    {
                        $changed_by_userid = $row[0];
                    }
                }
            }
           $query = "insert into user_status values(null, $status_id, $userid, current_timestamp, $changed_by_userid)";
            mysql_query($query, $mysql_link) or die(mysql_error());
        }
        print "<p class=highlight>User details updated</p>";
        print "<meta http-equiv='Refresh' content='2; url=useradmin.php'>";
    }

    include("updatepasswd.php");
    include("updateconfig.php");
}
elseif ($action == "edit" || $action == "add")
{
    if ($action == "edit")
    {
        if ($userid == "")
        {
            die("<p class=highlight>no userid specified</p>");
        }
        if (preg_match('/[^\d]/', $userid) > 0)
        {
            die("<p class=highlight>invalid userid</p>");
        }
        // get details from db
    //  $query = "SELECT userid, username, email, first_name, last_name, password, phone, street_address, other_address, city, state, zipcode, type, school, school_district, DATE_ADD(date_registered, INTERVAL $time_diff HOUR),double_gui,status_id,notes from user where userid=$userid";

     //$query = "SELECT userid, username, email, first_name, last_name, password, phone, street_address, other_address, city, state, zipcode, type, school, school_district, DATE_ADD(date_registered, INTERVAL $time_diff HOUR),double_gui,status_id,notes FROM user INNER JOIN parent_user ON user.userid=parent_user.userid;
 var_dump($userid);
$result = mysql_query($query, $mysql_link) or die("Query failed: $query\n" . mysql_last_error());
//$result = mysql_query($query, $mysql_link);
        if((!result) || (! mysql_num_rows($result)))
        {
            die("<p class=highlight>error getting details of user userid=$userid from db</p>");
        }

        if ($row = mysql_fetch_row($result)) 
        {
            $userid = $row[0];
            $username = $row[1];
            $email = $row[2];
            $first_name = $row[3];
            $last_name = $row[4];
		//$parent_last_name = $row[5];
            $password = $row[5];   // no it didn't print "<tr><td> will this print next to the password??????</td></tr>";
            $phone = $row[6];
            $street_address = $row[7];
            $other_address = $row[8];
            $city = $row[9];
            $state = $row[10];
            $zipcode = $row[11];
            $type = $row[12];
            $school = $row[13];
            $school_district = $row[14];
            $date_registered = $row[15];
            $double_gui = $row[16];
            $status_id = $row[17];
            $notes = $row[18];
            if ($type == "S")
            {
                $student_selected = "selected";
            }
            elseif ($type == "T")
            {
                $tutor_selected = "selected";
            }
            elseif ($type == "A")
            {
                $admin_selected = "selected";
            }
            elseif ($type == "K")
            {
                $tech_selected = "selected";
            }
        }
    }







// the line right below prints above the commented out print "where will this print????" line below.  
//print "<tr><td> really? where will this print??????</td></tr>";
    print "<form>";
// when not commented the below line prints right above the table for editing a user
//print "<tr><td> where will this print??????</td></tr>";
    print "<table>";
    if ($action == "edit")
    {
        print "<input type=hidden name=action value=update>";
        print "<input type=hidden name=userid value='$userid'>";
    }
    else
    {
        print "<input type=hidden name=action value=insert>";
    }

// action is not edit then has to be add mp
    print "<tr><td>Username:</td><td><input type=text maxlength=40 name=username value=\"$username\"> (REQUIRED)</td><tr>";
    print "<tr><td>Status:</td><td><select name=status_id><option value=\"\"></option>";

    // get statuses from db
    $query = "SELECT status_id,description from status";
    $result = mysql_query($query, $mysql_link);
    if(!result)
    {
        die("<p class=highlight>error getting status info from db</p>");
    }

    while ($row = mysql_fetch_row($result)) 
    {
        $sid = $row[0];
        $desc = $row[1];
        $selected = "";
        if ($sid == $status_id)
        {
            $selected = "selected";
        }
        print "<option $selected value=\"$sid\">$desc</option>";
    }
    print "</select><input type=submit onclick=\"window.open('statusadmin.php', 'status','width=600,height=600,scrollbars=yes'); return false\" value=\"ADD\"></td>";
    if ($userid != "")
    {
        print "<td style='padding-left: 50' rowspan=100 valign=top><iframe src=\"statuslog.php?id=$userid\"></iframe></td>";
    }
    print "</tr>";
    print "<tr><td>Email: </td><td><input type=text maxlength=80 name=email value=\"$email\"> (REQUIRED)</td><tr>";
// next 2 lines below have been altered by mark palmer.  I have added parent first name and last name in different positions.  well first i added parent first and alst name then the last version is the reorder of the input fields
   // print "<tr><td>First Name:</td><td><input type=text maxlength=40 name=first_name value=\"$first_name\"> (REQUIRED)</td><td>   </td><td>Parent First Name</td><td><input type=text maxlength=40 name=parent_first_name value=\"$parent_first_name\"> (REQUIRED)</td><tr>";
   // print "<tr><td>Last Name:</td><td><input type=text maxlength=40 name=last_name value=\"$last_name\"> (REQUIRED)</td><td>   </td><td>Parent Last Name</td><td><input type=text maxlength=40 name=parent_last_name value=\"$parent_last_name\"> (REQUIRED)<tr>";
   
    print "<tr><td> First Name:      </td><td><input type=text maxlength=40 name=first_name value=\"$first_name\"> (REQUIRED)</td><td>   </td><td>Last Name:</td><td><input type=text maxlength=40 name=last_name value=\"$last_name\"> (REQUIRED)</td><tr>";
    print "<tr><td>Parent First Name</td><td><input type=text maxlength=40 name=parent_first_name value=\"$parent_first_name\"> (REQUIRED)</td><td>   </td><td>Parent Last Name</td><td><input type=text maxlength=40 name=parent_last_name value=\"$parent_last_name\"> (REQUIRED)</td><tr>";

    



    print "<tr><td>Password:</td><td><input type=text maxlength=40 name=password value=\"$password\"> (REQUIRED)</td><tr>";
    print "<tr><td>Phone:</td><td><input type=text maxlength=20 name=phone value=\"$phone\"></td><tr>";
    print "<tr><td>Street Address:</td><td><input type=text maxlength=80 name=street_address value=\"$street_address\"> (REQUIRED)</td><tr>";
    print "<tr><td>Other Address:</td><td><input type=text maxlength=80 name=other_address value=\"$other_address\"></td><tr>";
    print "<tr><td>City:</td><td><input type=text maxlength=40 name=city value=\"$city\"> (REQUIRED)</td><tr>";
    print "<tr><td>State:</td><td><input size=2 type=text maxlength=2 name=state value=\"$state\"> (REQUIRED)</td><tr>";
    print "<tr><td>Zipcode:</td><td><input type=text maxlength=10 name=zipcode value=\"$zipcode\"> (REQUIRED)</td><tr>";
    print "<tr><td>Type:</td><td><select name=type><option value='S' $student_selected>Student</option><option value='T' $tutor_selected>Tutor</option><option value='A' $admin_selected>Admin</option><option value='K' $tech_selected>Tech</option></select>";
    print "<tr><td>School:</td><td><input type=text maxlength=80 name=school value=\"$school\"></td><tr>";
    print "<tr><td>School District:</td><td><input type=text maxlength=80 name=school_district value=\"$school_district\"></td><tr>";
    $checked = "";
    if ($double_gui == "Y")
    {
        $checked = "checked";
    }

    print "<tr><td>Use Double GUI:</td><td><input type=checkbox name=double_gui value=\"Y\" $checked></td><tr>";
    print "<tr><td>Notes:</td><td><textarea name=\"notes\">$notes</textarea></td></tr>";

    print "</table>";
//var_dump("$first_name");
//var_dump("$parent_first_name");
    if ($action == "add")
    {
        print "<p><input type=submit value=\"Add User\"></p>";
    }
    else
    {
        print "<p><input type=submit value=\"Update User Details\"></p>";
    }
    print "</form>";
    print "<hr>";
    print "<p>Upload multiple students from CSV file:</p>\n";
    print "<form enctype='multipart/form-data' action='csvupload.php' method='POST'>\n";
    print "<input type='hidden' name='MAX_FILE_SIZE' value='100000000'>\n";
    print "Choose a file to upload: <input name='csvfile' type='file'><br>\n";
    print "<input type='submit' value='Upload CSV File'>\n";
    print "</form>\n";
}
elseif ($action == "")
{
    $extra_tables = "";
    $qual = "";
    if ($userid != "")
    {
        if (preg_match('/[^\d]/', $userid) > 0)
        {
            die("<p class=highlight>invalid userid</p>");
        }
        $qual = "where userid=$userid";
    }
    if ($search != "")
    {
        $search = mysql_real_escape_string($search, $mysql_link);
        $qual = "where (username like '%$search%' or last_name like '%$search%' or email like '%$search%')";
    }
    if ($status_search != "")
    {
        $extra_tables = ",user_status";
        $qual = "where user_status.status_id=$status_search and user.userid=user_status.userid";
    }
    // get list of all users
// prints the table of all the user in the system. mp
    $query = "SELECT distinct user.userid, username, email, first_name, last_name, password, phone, street_address, other_address, city, state, zipcode, type, school, school_district, DATE_FORMAT(DATE_ADD(date_registered, INTERVAL $time_diff HOUR), '%m/%d/%y %l:%i%p'),double_gui,status.description,notes from (user$extra_tables) left join status on status.status_id=user.status_id $qual order by username";
    $result = mysql_query($query, $mysql_link) or die(mysql_error());
// the print statement below prints the table for each varibale.  need to add a column here before you fetch a new varible or print a new varible.  or it will just copy over something.   
   print "<table border=1><th>Userid</th><th>Username</th><th>Email</th><th>First Name</th><th>Last Name</th><th>Password</th><th>Phone</th><th>Street Address</th><th>Other Address</th><th>City</th><th>State</th><th>Zipcode</th><th>Type</th><th>School</th><th>School District</th><th>Sessions Attended</th><th>Date Registered</th><th>Double GUI</th><th>Status</th><th>Notes</th><th>DELETE</th><th>EDIT</th></tr>";
    if($result && mysql_num_rows($result)) 
    {
        while ($row = mysql_fetch_row($result)) 
        {
            $userid = $row[0];
            $username = $row[1];
            $email = $row[2];
            $first_name = $row[3];
            $last_name = $row[4];
            $password = $row[5];
            $phone = $row[6];
            $street_address = $row[7];
            $other_address = $row[8];
            $city = $row[9];
            $state = $row[10];
            $zipcode = $row[11];
            $type = $row[12];
            if ($type == "S")
            {
                $type = "Student";
                $student_selected = "selected";
            }
            elseif ($type == "T")
            {
                $type = "Tutor";
                $tutor_selected = "selected";
            }
            elseif ($type == "A")
            {
                $type = "Admin";
                $admin_selected = "selected";
            }
            elseif ($type == "K")
            {
                $type = "Tech";
                $tech_selected = "selected";
            }
            $school = $row[13];
            $school_district = $row[14];
            $date_registered = $row[15];
            $double_gui = $row[16];
            $status_desc = $row[17];
            $notes = $row[18];
            if ($double_gui != "Y")
            {
                $double_gui = "N";
            }

            if ($status_not != "")
            {
                $query2 = "select * from user_status where status_id=$status_not and userid=$userid";
                $result2 = mysql_query($query2, $mysql_link) or die(mysql_error());
                if($result2 && mysql_num_rows($result2)) 
                {
                    // ignore this one
                    continue;
                }
            }
            print "<tr>";
            print "<td>$userid</td>";
            print "<td>$username</td>";
            print "<td>$email</td>";
            print "<td>$first_name</td>";
            print "<td>$last_name</td>";
            print "<td>$password</td>";
            print "<td>$phone</td>";
            print "<td>$street_address</td>";
            print "<td>$other_address</td>";
            print "<td>$city</td>";
            print "<td>$state</td>";
            print "<td>$zipcode</td>";
            print "<td>$type</td>";
            print "<td>$school</td>";
            print "<td>$school_district</td>";
            print "<td><a href=\"listlogins.php?userid=$userid\">Sessions</a></td>";
            print "<td>$date_registered</td>";
            print "<td>$double_gui</td>";
            print "<td>Current status: $status_desc<br><button onclick=\"window.open('statuslog.php?id=$userid', 'statuslog$userid', 'width=600, height=600,scrollbars=yes')\">Show Log</button></td>";
            print "<td>$notes</td>";

            print "<td><form><input type=\"hidden\" name=\"action\" value=\"delete\"><input type=\"hidden\" name=\"userid\" value=\"$userid\"><input type=submit value=\"Delete\"></td></form>";
            print "<td><form><input type=\"hidden\" name=\"action\" value=\"edit\"><input type=\"hidden\" name=\"userid\" value=\"$userid\"><input type=submit value=\"Edit\"></form>";
            print "</tr>";
        }
    }
    else
    {
        print "<tr><td colspan=19>No users</td></tr>";
    }
    print "</table>";
    print "<form><input type=\"hidden\" name=\"action\" value=\"add\"><input type=submit value=\"Add New User\"></form>";
}
</script>
<a href=index.html>Back to Admin Home</a>
</body>
</html>

 

Link to comment
Share on other sites
ellegua220 Newbie

ellegua220

Posted
  • Author
Posted

I ran it again after changing the result statement back to its original form.  one error is the number of rows it is looking for or finding.  at line 384.  this is the if statement... are there any tools like a work bench that could help me debug php?  here are the errors i get after changing the result statement:

Notice: Undefined index: search in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 15

 

Notice: Undefined index: status_search in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 16

 

Notice: Undefined index: status_not in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 21

string(2) "24"

Notice: Undefined variable: query in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 383

 

Notice: Use of undefined constant result - assumed 'result' in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 384

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 384

 

error getting details of user userid=24 from db

 

 

Link to comment
Share on other sites
ellegua220 Newbie

ellegua220

Posted
  • Author
Posted

I have changed the query back to :

      $query = "SELECT userid, username, email, first_name, last_name, password, phone, street_address, other_address, city, state, zipcode, type, school, school_district, DATE_ADD(date_registered, INTERVAL $time_diff HOUR),double_gui,status_id,notes from user where userid=$userid";

 

the first set of errors:

 

Notice: Undefined index: action in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 13

 

Notice: Undefined index: userid in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 14

 

Notice: Undefined index: search in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 15

 

Notice: Undefined index: status_search in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 16

 

Notice: Undefined index: status_not in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 21

 

then when i click on edit these errors:

 

Notice: Undefined index: search in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 15

 

Notice: Undefined index: status_search in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 16

 

Notice: Undefined index: status_not in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 21

string(2) "57"

Notice: Use of undefined constant result - assumed 'result' in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 385

 

Notice: Undefined variable: parent_first_name in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 488

 

Notice: Undefined variable: parent_last_name in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 488

 

Notice: Undefined variable: tutor_selected in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 501

 

Notice: Undefined variable: admin_selected in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 501

 

Notice: Undefined variable: tech_selected in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 501

Link to comment
Share on other sites
ellegua220 Newbie

ellegua220

Posted
  • Author
Posted

I have taken care of all of the errors except the 2 fields that i want to assign with the joins...

 

Notice: Undefined variable: parent_first_name in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 525

 

Notice: Undefined variable: parent_last_name in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 525

 

here is my code now.  Please have a look and let me know if i did anything wrong:

<html>
<head>
<title>User Admin</title>
<link rel="STYLESHEET" href="../style.css" type="text/css">
</head>
<body>
<center>
<h1>User Admin</h1>
</center>
<script language="php">

// display errors and warnings added by mp
ini_set('display_errors', 1);

include("../db.php");

// declarations added by mark palmer to stop the warnings once the display errors statement was added
$action='';
$userid='';
$search='';
$status_search='';
$status_not='';
$result='';
$selected='';
$tutor_selected='';
$admin_selected='';
$tech_selected='';


/*
$action = $_GET['action'];
$userid = $_GET['userid'];
$search = $_GET['search'];
*/

// checking to see if variables were set before assigning them with $_GET added by mark palmer to stop the warnings once the display errors statement was added

if (isset($_GET['action']))
{$action = $_GET['action'];}

if (isset($_GET['userid']))
{$userid = $_GET['userid'];}

if (isset($_GET['search']))
{$search = $_GET['search'];}

if (isset($_GET['status_search']))
{$status_search = $_GET['status_search'];}

if (preg_match('/[^\d]/', $status_search) > 0)
{
    die("invalid status search id");
}

if (isset($_GET['status_not']))
{$status_not = $_GET['status_not'];}


if (preg_match('/[^\d]/', $status_not) > 0)
{
    die("invalid status search id");
}

if ($action == "delete")
{
    if ($userid == "")
    {
        die("<p class=highlight>no userid specified</p>");
    }
    if (preg_match('/[^\d]/', $userid) > 0)
    {
        die("<p class=highlight>invalid userid</p>");
    }
     
    $query = "DELETE FROM user WHERE userid=$userid";
    $result = mysql_query($query, $mysql_link) or die(mysql_error());
    $query = "DELETE FROM student_session WHERE userid=$userid";
    $result = mysql_query($query, $mysql_link) or die(mysql_error());
    $query = "DELETE FROM session_login WHERE userid=$userid";
    $result = mysql_query($query, $mysql_link) or die(mysql_error());

//below added by mark palmer to delete information from the new parent and parent_user tables when a user, future session and logins are deleted above.

/*$query = "DELETE FROM parent_user WHERE userid=$userid";  AT THE SAME TIME OR BEFORE IT NEEDS TO PULL OUT ALL OF THE PARENT_ID WHERE USERID=$USERID SO IT CAN DELETE FROM PARENT ID WHERE PARENTID=$PARENTID
    $result = mysql_query($query, $mysql_link) or die(mysql_error());
    $query = "DELETE FROM student_session WHERE userid=$userid";
    $result = mysql_query($query, $mysql_link) or die(mysql_error());
    $query = "DELETE FROM WHERE userid=$userid";
    $result = mysql_query($query, $mysql_link) or die(mysql_error());*/

    include("updatepasswd.php");
    include("updateconfig.php");
    print "<p class=highlight>User deleted</p>";
    print "<meta http-equiv='Refresh' content='2; url=useradmin.php'>";








}
elseif ($action == "update" || $action == "insert")
{
    // error checking preg_match('/[\047\042],varible') searches for single and double quotes  
    if (preg_match('/[^\d]/', $userid) > 0)
    {
        die("<p class=highlight>invalid userid</p>");
    }
    $username = $_GET['username'];
    if (preg_match('/[^\w\.@ ]/', $username) > 0)
    {
        die("<p class=highlight>invalid username: must only contain letters, digits, spaces, dots,  underscores and @</p>");
    }
    $email = $_GET['email'];
    if ($email != "")
    {
        if (! (preg_match('/@/', $email)))
        {
            die("<p class=highlight>invalid email address</p>");
        }
        if (! (preg_match('/\./', $email)))
        {
            die("<p class=highlight>invalid email address</p>");
        }
        if (preg_match('/[ \047\042\|<>]/', $email) > 0)
        {
            die("<p class=highlight>invalid email address</p>");
        }
    }

    $first_name = $_GET['first_name'];
    if (preg_match('/[\047\042]/', $first_name) > 0)
    {
        die("<p class=highlight>invalid first name</p>");
    }



    $last_name = $_GET['last_name'];
    if (preg_match('/[\047\042]/', $last_name) > 0)
    {
        die("<p class=highlight>invalid last name</p>");
    }


//next group of lines below added by Mark Palmer
// working
$parent_first_name=$_GET['parent_first_name'];
//var_dump($parent_first_name);

if (preg_match('/[\047\042]/', $parent_first_name) > 0)
    {
        die("<p class=highlight>invalid parent last name</p>");
    }

$parent_last_name=$_GET['parent_last_name'];
//var_dump($parent_last_name);

    if (preg_match('/[\047\042]/', $parent_last_name) > 0)
    {
        die("<p class=highlight>invalid parent last name</p>");
    } 

//var_dump($parent_first_name);
//var_dump($parent_last_name);
//print("<tr><td>next</td></tr>");

// previous lines added by mark pamer





    $password = $_GET['password'];
    if (preg_match('/[\047\042]/', $password) > 0)
    {
        die("<p class=highlight>invalid password</p>");
    }
    $phone = $_GET['phone'];
    if (preg_match('/[^ \d\.\/\(\)\+\-]/', $phone) > 0)
    {
        die("<p class=highlight>invalid phone number</p>");
    }
    $street_address = $_GET['street_address'];
    if (preg_match('/[\047\042]/', $street_address) > 0)
    {
        die("<p class=highlight>invalid street_address</p>");
    }
    $other_address = $_GET['other_address'];
    if (preg_match('/[\047\042]/', $other_address) > 0)
    {
        die("<p class=highlight>invalid other_address</p>");
    }
    $city = $_GET['city'];
    if (preg_match('/[\047\042]/', $city) > 0)
    {
        die("<p class=highlight>invalid city</p>");
    }
    $state = $_GET['state'];
    if (preg_match('/[^a-zA-Z]/', $state) > 0)
    {
        die("<p class=highlight>invalid state</p>");
    }
    $zipcode = $_GET['zipcode'];
    if (preg_match('/[\047\042]/', $zipcode) > 0)
    {
        die("<p class=highlight>invalid zipcode</p>");
    }
    $type = $_GET['type'];
    if ($type != "T" && $type != "A" && $type != "S" && $type != "K")
    {
        die("<p class=highlight>invalid type</p>");
    }
    $school = $_GET['school'];
    if (preg_match('/[\047\042]/', $school) > 0)
    {
        die("<p class=highlight>invalid school</p>");
    }
    $school_district = $_GET['school_district'];
    if (preg_match('/[\047\042]/', $school_district) > 0)
    {
        die("<p class=highlight>invalid school_district</p>");
    }
    $double_gui = $_GET['double_gui'];
    if ($double_gui != "Y")
    {
        $double_gui = "N";
    }
    $notes = $_GET['notes'];
    $status_id = $_GET['status_id'];






    if (preg_match('/[^0-9]/', $status_id) > 0)
    {
        die("<p class=highlight>invalid status</p>");
    }
    if ($status_id == "")
    {
        $status_id = "null";
    }

    if ($email == "")
    {
        die("<p class=highlight>email cannot be blank</p>");
    }
    if ($username == "")
    {
        die("<p class=highlight>username cannot be blank</p>");
    }

    if ($first_name == "")
    {
        die("<p class=highlight>first name cannot be blank</p>");
    }
    if ($last_name == "")
    {
        die("<p class=highlight>last name cannot be blank</p>");
    }
    if ($street_address == "")
    {
        die("<p class=highlight>street_address cannot be blank</p>");
    }
    if ($city == "")
    {
        die("<p class=highlight>city cannot be blank</p>");
    }
    if ($state == "")
    {
        die("<p class=highlight>state cannot be blank</p>");
    }
    if ($zipcode == "")
    {
        die("<p class=highlight>zipcode cannot be blank</p>");
    }
    if ($password == "")
    {
        die("<p class=highlight>password cannot be blank</p>");
    }
    if ($type == "")
    {
        die("<p class=highlight>type cannot be blank</p>");
    }
    
    if ($action == "insert")
    {
        // adding a new user
        if ($username == "")
        {
            die("<p class=highlight>username cannot be blank</p>");
        }
        if ($company_id == "")
        {
            $company_id = "null";
        }
     // david is using the sprintf to do some error checking.  It is inserting all of the fields into the table user.  it uses and string arg %s to get the esc checked field $notes   
        $query = sprintf("insert into user values(null, '$username', '$email', '$first_name', '$last_name', '$password', '$phone', '$street_address', '$other_address', '$city', '$state', '$zipcode', '$type', '$school', '$school_district', current_timestamp, '$double_gui', $status_id, '%s')",mysql_real_escape_string($notes, $mysql_link));
        mysql_query($query, $mysql_link) or die(mysql_error());
     $user_id= mysql_insert_id();

// var_dump($query);

// added by mark palmer inserts the null (for the pk) parents first and last name int the parent table  
     $query2 = sprintf("insert into parent values(null,'$parent_first_name','$parent_last_name')");

//var_dump($query2);

mysql_query($query2,$mysql_link) or die(mysql_error());


	// next line gets the parent id automatically created by after the insert into parents

$parent_id= mysql_insert_id();	
    var_dump($parent_id);	
var_dump($user_id);
$query3= sprintf("insert into parent_user values(null, '$parent_id','$user_id')");
var_dump($query3);

mysql_query($query3,$mysql_link) or die(mysql_error());


        if ($status_id != 'null' && $status_id != '')
        {
            $uid = mysql_insert_id($mysql_link);
            $changed_by = $_SERVER['REMOTE_USER'];
            $changed_by_userid = 'null';
            if ($changed_by != "")
            {
                $query = "select userid from user where username='$changed_by'";
                $result = mysql_query($query, $mysql_link) or die(mysql_error());
                if ($result)
                {
                    if ($row = mysql_fetch_row($result)) 
                    {
                        $changed_by_userid = $row[0];
                    }
                }
            }
            $query = "insert into user_status values(null, $status_id, $uid, current_timestamp, $changed_by_userid)";
            mysql_query($query, $mysql_link) or die(mysql_error());
        }
        print "<p class=highlight>User added</p>";
        print "<a href=\"useradmin.php?action=add\">Add More Users</a><br>";
        print "<a href=\"useradmin.php\">Back to User List</a><br>";
    }
    else
    {
        if ($userid == "")
        {
            die("<p class=highlight>no userid specified</p>");
        }

        // get old status so we can see if it changed
        $query = "select status_id from user where userid=$userid";
        $result = mysql_query($query, $mysql_link) or die(mysql_error());
        if((!result) || (! mysql_num_rows($result)))
        {
            die("<p class=highlight>error getting status of user userid=$userid from db</p>");
        }

        if ($row = mysql_fetch_row($result)) 
        {
            $old_status = $row[0];
        }

        $query = sprintf("UPDATE user set username='$username', email='$email', first_name='$first_name', last_name='$last_name', password='$password', phone='$phone', street_address='street_address', other_address='$other_address', city='$city', state='$state', zipcode='$zipcode', type='$type', school='$school', school_district='$school_district',double_gui='$double_gui',status_id=$status_id,notes='%s' where userid=$userid",
            mysql_real_escape_string($notes, $mysql_link));;
        ;
        mysql_query($query, $mysql_link) or die(mysql_error());
        if ($status_id != 'null' && $status_id != '' && $status_id != $old_status)
        {
            $changed_by = $_SERVER['REMOTE_USER'];
            $changed_by_userid = 'null';
            if ($changed_by != "")
            {
                $query = "select userid from user where username='$changed_by'";
                $result = mysql_query($query, $mysql_link) or die(mysql_error());
                if ($result)
                {
                    if ($row = mysql_fetch_row($result)) 
                    {
                        $changed_by_userid = $row[0];
                    }
                }
            }
           $query = "insert into user_status values(null, $status_id, $userid, current_timestamp, $changed_by_userid)";
            mysql_query($query, $mysql_link) or die(mysql_error());
        }
        print "<p class=highlight>User details updated</p>";
        print "<meta http-equiv='Refresh' content='2; url=useradmin.php'>";
    }

    include("updatepasswd.php");
    include("updateconfig.php");
}
elseif ($action == "edit" || $action == "add")
{
    if ($action == "edit")
    {
        if ($userid == "")
        {
            die("<p class=highlight>no userid specified</p>");
        }
        if (preg_match('/[^\d]/', $userid) > 0)
        {
            die("<p class=highlight>invalid userid</p>");
        }
        // get details from db
     $query = "SELECT userid, username, email, first_name, last_name, password, phone, street_address, other_address, city, state, zipcode, type, school, school_district, DATE_ADD(date_registered, INTERVAL $time_diff HOUR),double_gui,status_id,notes from user where userid=$userid";

     //$query = "SELECT user.userid, user.username, user.email, user.first_name, user.last_name, user.password, user.phone, user.street_address, user.other_address, user.city, user.state, user.zipcode, user.type, user.school, user.school_district, DATE_ADD(user.date_registered, INTERVAL $time_diff HOUR),user.double_gui,user.status_id,user.notes FROM user INNER JOIN parent_user ON user.userid=parent_user.userid;
//$query="SELECT * FROM user u LEFT JOIN parent_user pu ON pu.userid = u.userid LEFT JOIN parent p ON p.parent_id = pu.parent_id WHERE u.userid = $userid"
var_dump($userid);

//$result = mysql_query($query, $mysql_link) or die("Query failed: $query\n" . mysql_last_error());
$result = mysql_query($query, $mysql_link);
        if((!$result) || (! mysql_num_rows($result)))
        {
            die("<p class=highlight>error getting details of user userid=$userid from db</p>");
        }

        if ($row = mysql_fetch_row($result)) 
        {
            $userid = $row[0];
            $username = $row[1];
            $email = $row[2];
            $first_name = $row[3];
            $last_name = $row[4];
		//$parent_last_name = $row[5];
            $password = $row[5];   // no it didn't print "<tr><td> will this print next to the password??????</td></tr>";
            $phone = $row[6];
            $street_address = $row[7];
            $other_address = $row[8];
            $city = $row[9];
            $state = $row[10];
            $zipcode = $row[11];
            $type = $row[12];
            $school = $row[13];
            $school_district = $row[14];
            $date_registered = $row[15];
            $double_gui = $row[16];
            $status_id = $row[17];
            $notes = $row[18];
            if ($type == "S")
            {
                $student_selected = "selected";
            }
            elseif ($type == "T")
            {
                $tutor_selected = "selected";
            }
            elseif ($type == "A")
            {
                $admin_selected = "selected";
            }
            elseif ($type == "K")
            {
                $tech_selected = "selected";
            }
        }
    }







// the line right below prints above the commented out print "where will this print????" line below.  
//print "<tr><td> really? where will this print??????</td></tr>";
    print "<form>";
// when not commented the below line prints right above the table for editing a user
//print "<tr><td> where will this print??????</td></tr>";
    print "<table>";
    if ($action == "edit")
    {
        print "<input type=hidden name=action value=update>";
        print "<input type=hidden name=userid value='$userid'>";
    }
    else
    {
        print "<input type=hidden name=action value=insert>";
    }

// action is not edit then has to be add mp
    print "<tr><td>Username:</td><td><input type=text maxlength=40 name=username value=\"$username\"> (REQUIRED)</td><tr>";
    print "<tr><td>Status:</td><td><select name=status_id><option value=\"\"></option>";

    // get statuses from db
    $query = "SELECT status_id,description from status";
    $result = mysql_query($query, $mysql_link);
    if(!result)
    {
        die("<p class=highlight>error getting status info from db</p>");
    }

    while ($row = mysql_fetch_row($result)) 
    {
        $sid = $row[0];
        $desc = $row[1];
        $selected = "";
        if ($sid == $status_id)
        {
            $selected = "selected";
        }
        print "<option $selected value=\"$sid\">$desc</option>";
    }
    print "</select><input type=submit onclick=\"window.open('statusadmin.php', 'status','width=600,height=600,scrollbars=yes'); return false\" value=\"ADD\"></td>";
    if ($userid != "")
    {
        print "<td style='padding-left: 50' rowspan=100 valign=top><iframe src=\"statuslog.php?id=$userid\"></iframe></td>";
    }
    print "</tr>";
    print "<tr><td>Email: </td><td><input type=text maxlength=80 name=email value=\"$email\"> (REQUIRED)</td><tr>";
// next 2 lines below have been altered by mark palmer.  I have added parent first name and last name in different positions.  well first i added parent first and alst name then the last version is the reorder of the input fields
   // print "<tr><td>First Name:</td><td><input type=text maxlength=40 name=first_name value=\"$first_name\"> (REQUIRED)</td><td>   </td><td>Parent First Name</td><td><input type=text maxlength=40 name=parent_first_name value=\"$parent_first_name\"> (REQUIRED)</td><tr>";
   // print "<tr><td>Last Name:</td><td><input type=text maxlength=40 name=last_name value=\"$last_name\"> (REQUIRED)</td><td>   </td><td>Parent Last Name</td><td><input type=text maxlength=40 name=parent_last_name value=\"$parent_last_name\"> (REQUIRED)<tr>";
   
    print "<tr><td> First Name:      </td><td><input type=text maxlength=40 name=first_name value=\"$first_name\"> (REQUIRED)</td><td>   </td><td>Last Name:</td><td><input type=text maxlength=40 name=last_name value=\"$last_name\"> (REQUIRED)</td><tr>";
    print "<tr><td>Parent First Name</td><td><input type=text maxlength=40 name=parent_first_name value=\"$parent_first_name\"> (REQUIRED)</td><td>   </td><td>Parent Last Name</td><td><input type=text maxlength=40 name=parent_last_name value=\"$parent_last_name\"> (REQUIRED)</td><tr>";

  


    print "<tr><td>Password:</td><td><input type=text maxlength=40 name=password value=\"$password\"> (REQUIRED)</td><tr>";
    print "<tr><td>Phone:</td><td><input type=text maxlength=20 name=phone value=\"$phone\"></td><tr>";
    print "<tr><td>Street Address:</td><td><input type=text maxlength=80 name=street_address value=\"$street_address\"> (REQUIRED)</td><tr>";
    print "<tr><td>Other Address:</td><td><input type=text maxlength=80 name=other_address value=\"$other_address\"></td><tr>";
    print "<tr><td>City:</td><td><input type=text maxlength=40 name=city value=\"$city\"> (REQUIRED)</td><tr>";
    print "<tr><td>State:</td><td><input size=2 type=text maxlength=2 name=state value=\"$state\"> (REQUIRED)</td><tr>";
    print "<tr><td>Zipcode:</td><td><input type=text maxlength=10 name=zipcode value=\"$zipcode\"> (REQUIRED)</td><tr>";
    print "<tr><td>Type:</td><td><select name=type><option value='S' $student_selected>Student</option><option value='T' $tutor_selected>Tutor</option><option value='A' $admin_selected>Admin</option><option value='K' $tech_selected>Tech</option></select>";
    print "<tr><td>School:</td><td><input type=text maxlength=80 name=school value=\"$school\"></td><tr>";
    print "<tr><td>School District:</td><td><input type=text maxlength=80 name=school_district value=\"$school_district\"></td><tr>";
    $checked = "";
    if ($double_gui == "Y")
    {
        $checked = "checked";
    }

    print "<tr><td>Use Double GUI:</td><td><input type=checkbox name=double_gui value=\"Y\" $checked></td><tr>";
    print "<tr><td>Notes:</td><td><textarea name=\"notes\">$notes</textarea></td></tr>";

    print "</table>";
//var_dump("$first_name");
//var_dump("$parent_first_name");
    if ($action == "add")
    {
        print "<p><input type=submit value=\"Add User\"></p>";
    }
    else
    {
        print "<p><input type=submit value=\"Update User Details\"></p>";
    }
    print "</form>";
    print "<hr>";
    print "<p>Upload multiple students from CSV file:</p>\n";
    print "<form enctype='multipart/form-data' action='csvupload.php' method='POST'>\n";
    print "<input type='hidden' name='MAX_FILE_SIZE' value='100000000'>\n";
    print "Choose a file to upload: <input name='csvfile' type='file'><br>\n";
    print "<input type='submit' value='Upload CSV File'>\n";
    print "</form>\n";
}
elseif ($action == "")
{
    $extra_tables = "";
    $qual = "";
    if ($userid != "")
    {
        if (preg_match('/[^\d]/', $userid) > 0)
        {
            die("<p class=highlight>invalid userid</p>");
        }
        $qual = "where userid=$userid";
    }
    if ($search != "")
    {
        $search = mysql_real_escape_string($search, $mysql_link);
        $qual = "where (username like '%$search%' or last_name like '%$search%' or email like '%$search%')";
    }
    if ($status_search != "")
    {
        $extra_tables = ",user_status";
        $qual = "where user_status.status_id=$status_search and user.userid=user_status.userid";
    }
    // get list of all users
// prints the table of all the user in the system. mp
    $query = "SELECT distinct user.userid, username, email, first_name, last_name, password, phone, street_address, other_address, city, state, zipcode, type, school, school_district, DATE_FORMAT(DATE_ADD(date_registered, INTERVAL $time_diff HOUR), '%m/%d/%y %l:%i%p'),double_gui,status.description,notes from (user$extra_tables) left join status on status.status_id=user.status_id $qual order by username";
    $result = mysql_query($query, $mysql_link) or die(mysql_error());
// the print statement below prints the table for each varibale.  need to add a column here before you fetch a new varible or print a new varible.  or it will just copy over something.   
   print "<table border=1><th>Userid</th><th>Username</th><th>Email</th><th>First Name</th><th>Last Name</th><th>Password</th><th>Phone</th><th>Street Address</th><th>Other Address</th><th>City</th><th>State</th><th>Zipcode</th><th>Type</th><th>School</th><th>School District</th><th>Sessions Attended</th><th>Date Registered</th><th>Double GUI</th><th>Status</th><th>Notes</th><th>DELETE</th><th>EDIT</th></tr>";
    if($result && mysql_num_rows($result)) 
    {
        while ($row = mysql_fetch_row($result)) 
        {
            $userid = $row[0];
            $username = $row[1];
            $email = $row[2];
            $first_name = $row[3];
            $last_name = $row[4];
            $password = $row[5];
            $phone = $row[6];
            $street_address = $row[7];
            $other_address = $row[8];
            $city = $row[9];
            $state = $row[10];
            $zipcode = $row[11];
            $type = $row[12];
            if ($type == "S")
            {
                $type = "Student";
                $student_selected = "selected";
            }
            elseif ($type == "T")
            {
                $type = "Tutor";
                $tutor_selected = "selected";
            }
            elseif ($type == "A")
            {
                $type = "Admin";
                $admin_selected = "selected";
            }
            elseif ($type == "K")
            {
                $type = "Tech";
                $tech_selected = "selected";
            }
            $school = $row[13];
            $school_district = $row[14];
            $date_registered = $row[15];
            $double_gui = $row[16];
            $status_desc = $row[17];
            $notes = $row[18];
            if ($double_gui != "Y")
            {
                $double_gui = "N";
            }

            if ($status_not != "")
            {
                $query2 = "select * from user_status where status_id=$status_not and userid=$userid";
                $result2 = mysql_query($query2, $mysql_link) or die(mysql_error());
                if($result2 && mysql_num_rows($result2)) 
                {
                    // ignore this one
                    continue;
                }
            }
            print "<tr>";
            print "<td>$userid</td>";
            print "<td>$username</td>";
            print "<td>$email</td>";
            print "<td>$first_name</td>";
            print "<td>$last_name</td>";
            print "<td>$password</td>";
            print "<td>$phone</td>";
            print "<td>$street_address</td>";
            print "<td>$other_address</td>";
            print "<td>$city</td>";
            print "<td>$state</td>";
            print "<td>$zipcode</td>";
            print "<td>$type</td>";
            print "<td>$school</td>";
            print "<td>$school_district</td>";
            print "<td><a href=\"listlogins.php?userid=$userid\">Sessions</a></td>";
            print "<td>$date_registered</td>";
            print "<td>$double_gui</td>";
            print "<td>Current status: $status_desc<br><button onclick=\"window.open('statuslog.php?id=$userid', 'statuslog$userid', 'width=600, height=600,scrollbars=yes')\">Show Log</button></td>";
            print "<td>$notes</td>";

            print "<td><form><input type=\"hidden\" name=\"action\" value=\"delete\"><input type=\"hidden\" name=\"userid\" value=\"$userid\"><input type=submit value=\"Delete\"></td></form>";
            print "<td><form><input type=\"hidden\" name=\"action\" value=\"edit\"><input type=\"hidden\" name=\"userid\" value=\"$userid\"><input type=submit value=\"Edit\"></form>";
            print "</tr>";
        }
    }
    else
    {
        print "<tr><td colspan=19>No users</td></tr>";
    }
    print "</table>";
    print "<form><input type=\"hidden\" name=\"action\" value=\"add\"><input type=submit value=\"Add New User\"></form>";
}
</script>
<a href=index.html>Back to Admin Home</a>
</body>
</html>

Link to comment
Share on other sites
btherl Member

btherl

Posted
Posted

Sorry, yes it is mysql_error() not mysql_last_error().

 

"Undefined variable: parent_first_name" means you are using $parent_first_name before you set it.  If you have code which sets $parent_first_name, then that code is either not working correctly or it is not being executed.  You can debug this by adding print statements throughout your code so you can check which parts are being executed, and why.

 

There is too much code there for me to look through.  Does the code work now?

Link to comment
Share on other sites
ellegua220 Newbie

ellegua220

Posted
  • Author
Posted

no its still not working. I thought the join would give parent_first_name it's value but I'm don't know how to write it correctly:

 

    $query = "SELECT user.userid, user.username, user.email, user.first_name, user.last_name, user.password, user.phone, user.street_address, user.other_address, user.city, user.state, user.zipcode, user.type, user.school, user.school_district, DATE_ADD(user.date_registered, INTERVAL $time_diff HOUR),user.double_gui,user.status_id,user.notes, parent_user.userid, parent.parent_first_name, parent.parent_last.name FROM user INNER JOIN parent_user ON user.userid=parent_user.userid AND parent_user.parent_id=parent.parent_id";

 

Link to comment
Share on other sites
ellegua220 Newbie

ellegua220

Posted
  • Author
Posted

I also tried this:

 

$query = "SELECT user.userid, user.username, user.email, user.first_name, user.last_name, user.password, user.phone, user.street_address, user.other_address, user.city, user.state, user.zipcode, user.type, user.school, user.school_district, DATE_ADD(user.date_registered, INTERVAL $time_diff HOUR),user.double_gui,user.status_id,user.notes, parent_user.userid, parent.parent_first_name, parent.parent_last.name FROM user JOIN parent_user ON user.userid=parent_user.userid JOIN parent ON parent_user.parent_id=parent.parent_id";

 

Link to comment
Share on other sites
ellegua220 Newbie

ellegua220

Posted
  • Author
Posted

Ok. aparently I can't use var_dump on the variables from the select statment

$parent.parent_last_name=parent.parent_last_name;

var_dump($parent.parent_last_name);

was causing an error and giving the white screen.  why?

so now it's working a little better. 

 

I am still gettting:

Notice: Undefined variable: parent_first_name in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 531

 

Notice: Undefined variable: parent_last_name in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 531

 

I am not sure how to assign the joined table values to variables.  The user table is assigned with an array of some kind.  how do I reference the other tables?:  

if ($row = mysql_fetch_row($result)) 
        {

            $userid = $row[0];
            $username = $row[1];
            $email = $row[2];
            $first_name = $row[3];
            $last_name = $row[4];
		//$parent_last_name = $row[5];
            $password = $row[5];   // no it didn't print "<tr><td> will this print next to the password??????</td></tr>";
            $phone = $row[6];
            $street_address = $row[7];
            $other_address = $row[8];
            $city = $row[9];
            $state = $row[10];
            $zipcode = $row[11];
            $type = $row[12];
            $school = $row[13];
            $school_district = $row[14];
            $date_registered = $row[15];
            $double_gui = $row[16];
            $status_id = $row[17];
            $notes = $row[18];
		//$parent_user.userid =$row[19];
            if ($type == "S")
            {
                $student_selected = "selected";
            }
            elseif ($type == "T")
            {
                $tutor_selected = "selected";
            }
            elseif ($type == "A")
            {
                $admin_selected = "selected";
            }
            elseif ($type == "K")
            {
                $tech_selected = "selected";
            }

Link to comment
Share on other sites
ellegua220 Newbie

ellegua220

Posted
  • Author
Posted

i don't see it?  what do you mean?   

Undefined variable: parent_first_name in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 531

 

Notice: Undefined variable: paren_last_name in /var/www/vhosts/testing.itsalllive.com/httpdocs/conference/admin/useradmin.php on line 531

 

However I do know now that the row is the position in the select statement. 

What if the select is a wildcard such as in this select:

$query="SELECT * FROM user u LEFT JOIN parent_user pu ON pu.userid = u.userid LEFT JOIN parent p ON p.parent_id = pu.parent_id WHERE u.userid = $userid";

 

this one is working except i am getting a user id and parent_user_id in the parent first and last name fields.. all othr fields are in place...  where is this defined?

Link to comment
Share on other sites
btherl Member

btherl

Posted
Posted

Results are numbered in the order they appear in the select.  Since you put userid, parent_first_name and parent_last_name last in the list, they will be numbered 19, 20 and 21 respectively.

 

Alternatively you can use mysql_fetch_array() and fetch columns by name instead of by number.  This is generally a better approach, but I don't recommend changing it in your code now as you will end up having trouble due to columns with the same name, such as userid.  If you used this approach you would have to use the sql "AS" to rename some columns to different names.

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • 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.