Page 1 of 1

Order of Results Continually Changing

Posted: Sat Aug 16, 2014 10:07 am
by diseman
Hi All,

Looking for some help with my php 'learning' project.

Seems every time I save another name to my list of names the order they are presented back to me is changed. In other words, if I enter/save three names - the order of those names being presented back to me changes each time I click the save button. Not a huge deal, but tell that to my OCD side! ;)

Here is the code:

Code: Select all

$sql = "Delete FROM notifications WHERE username = '".$requestor."' " ;
$result = mysqli_query($con, $sql) or die (mysqli_error($con));

	foreach ($_POST['txt'] as $key => $val)     {

        if (!empty($val))   {

			$txtbox 		= mysqli_real_escape_string($con, $_POST['txt']		        [$key]);
			$relationship 	= mysqli_real_escape_string($con, $_POST['relationship']	[$key]);
			$address		= mysqli_real_escape_string($con, $_POST['address']		[$key]);
			$email		= mysqli_real_escape_string($con, $_POST['email']		[$key]);
			$phone		= mysqli_real_escape_string($con, $_POST['phone']		[$key]);

		    $fields =  "userid 		    =	'$userid',
                        username        =   '$username',
                        name		    =	'$txtbox',
						relationship	=	'$relationship',
						address		    =	'$address',
						email		    =	'$email',
						phone		    =	'$phone'";

		   $sql = "INSERT notifications SET ".$fields." ";
		   mysqli_query($con, $sql) or die (mysqli_error($con));;

} // if
} // foreach
} // post

$txtbox = $relationship = $address = $email = $phone = array();

// Default number of empty dynamic Rows on the form & set variables

for ($i = 0; $i < 6; $i++)

	{
	  	$txtbox[$i] 		= "";
	  	$relationship[$i]	= "";
	  	$address[$i]		= "";
	  	$email[$i]		        = "";
	 	$phone[$i]		        = "";
  	}

$query =  "SELECT name, relationship, address, email, phone FROM notifications WHERE username = '".$requestor."' " ;
$result = mysqli_query($con, $query);

    if (!$result) die(mysql_error());

        else    {

            $i = 0;

	    while ($row = mysqli_fetch_object($result))     {

	    $txtbox[$i] 		= $row->name;
	    $relationship[$i]	= $row->relationship;
	    $address[$i]		= $row->address;
	    $email[$i]		= $row->email;
	    $phone[$i]		= $row->phone;
	    $i++;

}
	mysqli_free_result($result);
}
There is no ID in the db for these records, but even with an ID they were presented in different order each time as well.

Ideally, I would prefer to UPDATE, but that fix has proved to be illusive as well. :)

Seems to me they should be added to the db the same way they are listed in my form and then displayed back to me the same way.

Thank you in advance for any help,

Michael

Re: Order of Results Continually Changing

Posted: Sat Aug 16, 2014 11:34 am
by Celauran
Use an ORDER BY clause in your statement to have them ordered how you'd like.

Now why is there no ID column? Do you at least have a primary key set? If not, you'll have a bad time as that table grows.

Re: Order of Results Continually Changing

Posted: Sat Aug 16, 2014 11:36 am
by Celauran
As for the update, deleting and reinserting is definitely not an optimal approach. I asked in my previous post if you have a primary key defined. Are there any other constraints on the table? If all columns can have multiple rows with the same value, well, determining what's what is going to get hard fast.

Re: Order of Results Continually Changing

Posted: Sat Aug 16, 2014 2:48 pm
by diseman
Ok, thank you for the help. I'll look into Order By now and add the ID back as well.

Not sure I understood "As for the update, deleting and reinserting is definitely not an optimal approach." Where were you going with that, so that I might do some reading-up on that as well.

Thank you again.

Re: Order of Results Continually Changing

Posted: Sat Aug 16, 2014 2:51 pm
by Celauran
You had already mentioned a better approach: UPDATE. Trouble is, without a primary key, you're going to have a hard time ensuring you update the right row. You want a unique constraint on your WHERE clause to ensure that, which is usually done via primary key.

Re: Order of Results Continually Changing

Posted: Sat Aug 16, 2014 3:16 pm
by diseman
Ok, that was actually a pretty simple fix. Thanks for your guidance.

Here's where I'm at now:

Code: Select all

if (isset($_POST['submit']))    {

    $result = mysqli_query($con,"SELECT * FROM users WHERE  username = '".$requestor."' ") ;

	while ($row = mysqli_fetch_object($result)) {

	$userid = $row->userid;
    $username = $row->username;

}

    mysqli_free_result($result);

$result = mysqli_query($con, "Delete FROM notifications WHERE username = '".$requestor."' ");

	foreach ($_POST['txt'] as $key => $val)     {

        if (!empty($val))   {

			$txtbox 		= mysqli_real_escape_string($con, $_POST['txt']		        [$key]);
			$relationship 	= mysqli_real_escape_string($con, $_POST['relationship']	[$key]);
			$address		= mysqli_real_escape_string($con, $_POST['address']		    [$key]);
			$email		    = mysqli_real_escape_string($con, $_POST['email']		    [$key]);
			$phone		    = mysqli_real_escape_string($con, $_POST['phone']		    [$key]);

		    $fields =  "userid 		    =	'$userid',
                        username        =   '$username',
                        name		    =	'$txtbox',
						relationship	=	'$relationship',
						address		    =	'$address',
						email		    =	'$email',
						phone		    =	'$phone'";

		   $sql = "INSERT notifications SET ".$fields." ";
		   mysqli_query($con, $sql) or die (mysqli_error($con));;

} // if
} // foreach
} // post

$txtbox = $relationship = $address = $email = $phone = array();

// Default number of empty dynamic Rows on the form & set variables

for ($i = 0; $i < 6; $i++)

	{
	  	$txtbox[$i] 		= "";
	  	$relationship[$i]	= "";
	  	$address[$i]		= "";
	  	$email[$i]		    = "";
	 	$phone[$i]		    = "";
  	}

$result = mysqli_query($con, "SELECT name, relationship, address, email, phone FROM notifications WHERE username = '".$requestor."' ORDER BY id ");

    if (!$result) die(mysql_error());

        else    {

            $i = 0;

	    while ($row = mysqli_fetch_object($result))     {

	    $txtbox[$i] 		= $row->name;
	    $relationship[$i]	= $row->relationship;
	    $address[$i]		= $row->address;
	    $email[$i]		    = $row->email;
	    $phone[$i]		    = $row->phone;
	    $i++;

}
	mysqli_free_result($result);
}

mysqli_close($con);
Now, my ID's are increasing pretty rapidly with INSERT each time. I'm gonna read-up on UPDATE and see if I can figure out how to use it with the ID.

Thanks again Celauran.

Re: Order of Results Continually Changing

Posted: Sat Aug 16, 2014 7:36 pm
by diseman
Hi again Celauran & all,

Well, I can get the UPDATE to work on a more simple page where the user will only have one row of data, but on the following page with the possibility of a single user having multiple rows of data I simply can't figure out a method that works.

Below is my current code if you have any time to take a look.

Please keep in mind I'm only a little more skillful than a complete noob, so examples or actually changing the code where the problem is are much appreciated as I can learn from that and apply along the way.

I've added the ID and Primary Key as you suggested

Code: Select all

<?php

include_once ("../_includes/_connect.php");
include_once ("../_includes/functions.php");

timeout();

if (isset($_POST['submit'])) {

    $result = mysqli_query($con, "SELECT userid, username FROM users WHERE username = '".$requestor."' ") ;

    while ($row = mysqli_fetch_object($result)) {
        $userid = $row->userid;
        $username = $row->username;
    }

    mysqli_free_result($result);

    $result = mysqli_query($con, "Delete FROM notifications WHERE username = '".$requestor."' ");

    foreach ($_POST['txt'] as $key => $val) {

        if (!empty($val)) {
	    $txtbox = mysqli_real_escape_string($con, $_POST['txt'][$key]);
	    $relationship = mysqli_real_escape_string($con, $_POST['relationship'][$key]);
	    $address = mysqli_real_escape_string($con, $_POST['address'][$key]);
	    $email = mysqli_real_escape_string($con, $_POST['email'][$key]);
	    $phone = mysqli_real_escape_string($con, $_POST['phone'][$key]);

	    $table_fields = "userid = '$userid',
                             username = '$username',
                             name = '$txtbox',
	  	             relationship = '$relationship',
                             address = '$address',
                             email = '$email',
                             phone = '$phone'";

	    $result = mysqli_query($con, "INSERT notifications SET ".$table_fields." ");

        } // if
    } // foreach
} // post

$txtbox = $relationship = $address = $email = $phone = array();

// Default number of empty dynamic Rows on the form & set variables

for ($i = 0; $i < 6; $i++) {
    $txtbox[$i] = "";
    $relationship[$i] = "";
    $address[$i] = "";
    $email[$i] = "";
    $phone[$i] = "";
}

$result = mysqli_query($con, "SELECT name, relationship, address, email, phone FROM notifications WHERE username = '".$requestor."' ") ;

if (!$result) die(mysql_error());
else {
    $i = 0;

    while ($row = mysqli_fetch_object($result))     {
        $txtbox[$i] = $row->name;
	$relationship[$i] = $row->relationship;
	$address[$i] = $row->address;
	$email[$i] = $row->email;
	$phone[$i] = $row->phone;
	$i++;

    }
    mysqli_free_result($result);
}

mysqli_close($con);

?>
Thanks in advance for any help

Re: Order of Results Continually Changing

Posted: Sat Aug 16, 2014 10:29 pm
by Celauran
What does this data represent? Why would a user have multiple rows? None of the data should be duplicated across rows, so you should still be able to target the specific row you're trying to update.

Re: Order of Results Continually Changing

Posted: Sun Aug 17, 2014 8:26 am
by diseman
The data represents nothing more than family, friends, & relatives information.

So, I could add, for example, a person's name, relationship to me (i.e. wife), address, email, phone. Then I could add my son's, best friend, boss, etc. The only field that could/should duplicate is the 'relationship' field.

EDIT: Oh, and I have a userid (int), username (email address), and now an id (int).

Re: Order of Results Continually Changing

Posted: Sun Aug 17, 2014 10:35 am
by diseman
additionally....

I tried this for the past couple hours, but didn't have any success. The first row entry goes into the ether and the second row entry is added to the db.

Code: Select all

if (isset($_POST['submit']))    {

    $result = mysqli_query($con, "SELECT userid, username FROM users WHERE username = '".$requestor."' ") ;

	while ($row = mysqli_fetch_object($result)) {

	    $userid     = $row  ->  userid;
            $username   = $row  ->  username;
}

foreach ($_POST['txt'] as $key => $val)     {

        if (!empty($val))   {

			$txtbox 		= mysqli_real_escape_string($con, $_POST['txt']		        [$key]);
			$relationship 	= mysqli_real_escape_string($con, $_POST['relationship']	[$key]);
			$address		= mysqli_real_escape_string($con, $_POST['address']		[$key]);
			$email		= mysqli_real_escape_string($con, $_POST['email']		[$key]);
			$phone		= mysqli_real_escape_string($con, $_POST['phone']		[$key]);

		    $table_fields =             "userid 		    =	'$userid',
                                                        username             =   '$username',
                                                        name		    =	'$txtbox',
				        		relationship	    =	'$relationship',
						        address		    =	'$address',
        						email		    =	'$email',
		        				phone		    =	'$phone'";

            $result = mysqli_query($con, "Select * FROM notifications WHERE username = '".$requestor."' ");

                printf("Select returned %d rows.\n", $result->num_rows); // Confirm presence of data

            if (mysqli_num_rows($result) >= 1) 	{

                $task1 = mysqli_query($con, "UPDATE notifications SET ".$table_fields." WHERE username = '".$requestor."' ");

                    }   else {

		        $task2 = mysqli_query($con, "INSERT notifications SET ".$table_fields." ");

                    }

} // if
} // foreach

mysqli_free_result($result);

} // post

Re: Order of Results Continually Changing

Posted: Sun Aug 17, 2014 12:08 pm
by Celauran
So if I'm understanding things correctly, the users table tracks the users in your system and the notifications table is a 'belongs to' relationship with userid and username both acting as foreign keys?

Re: Order of Results Continually Changing

Posted: Sun Aug 17, 2014 12:46 pm
by diseman
Users table is just:

Code: Select all

id int(32) auto_increment NOT NULL,
userid int(32) unsigned NOT NULL,
username varchar(255) NOT NULL,
password varchar(255) NOT NULL,
usertype tinyint(1) NOT NULL,
account varchar(10)NOT NULL,
active_state tinyint(1) NOT NULL,
start_date varchar(10) NOT NULL,
start_date_unix int(32) unsigned  NOT NULL,

PRIMARY KEY (id)
and notifications table:

Code: Select all

id int(32) unsigned auto_increment NOT NULL,
userid int(32) unsigned NOT NULL,
username varchar(255) NOT NULL,
name varchar(255),
relationship varchar(255),
address varchar(255),
email varchar(255),
phone varchar(12),

PRIMARY KEY (id)
Just to be clear, RELATIONSHIP is only the relationship a person is to me (i.e. wife, son, friend, boss, etc.). I don't want that word to be confused with some PHP meaning or some relationship between some code.

All I'm trying to learn is how to insert/update a list of people, their relationship to me, their address, email, and phone

I hope that makes more sense.

Thank you again for any/all help.

Re: Order of Results Continually Changing

Posted: Sun Aug 17, 2014 6:37 pm
by Celauran
No, I get that. The column called relationship denotes their relationship to you. What is the difference between the userid and username columns in the two tables, or are they used to show that entries in one table belong to an entry in another table? In either case, seeing the schema is definitely helpful. For your UPDATE queries, I would suggest perhaps including the id column in your WHERE clause rather than userid or username. id, being the primary key, is guaranteed to be unique.

Re: Order of Results Continually Changing

Posted: Sun Aug 17, 2014 8:19 pm
by diseman
Another few hours and nothing. At this point, I'll need to see it if you're willing and able.

Frustrated,

Michael