A little help optimizing this.

[Syre]

Okay so basically, I wanted to design a system where I could save/load checkboxes to the DB (using mysqli). I will be creating an item checklist for a game, and well, this is rather useful.

Here's the stuff.


saving(write.php)

<?phpinclude('config.php');
for ($i = 0;$i < sizeof($checkb);$i++) {
if (isset($_POST["Check".$i.""])){
$checkb[$i] = 'checked';
} else {
$checkb[$i] = '!checked';
}
}
for ($z = 0; $z < sizeof($checkb);$z++) {
$query = "UPDATE `test` SET info".$z." = '".$checkb[$z]."' WHERE ip = '" . $_SERVER['REMOTE_ADDR'] ."'";
if (!mysqli_query($con, $query)) {
	die('Error: ' . mysqli_error($con));
	}
	}
	echo "".sizeof($checkb)." records updated";	
?>

loading(load.php)

<?phpinclude ('config.php');
if ($isLogged) {
for ($s = 0; $s < sizeof($checkb); $s++) {
$query = "SELECT * FROM `test` WHERE ip = '" . $_SERVER['REMOTE_ADDR'] ."'";
}
$result = mysqli_query($con, $query);
$h = 0;
while ($row = mysqli_fetch_array($result)) {
$isLogged = true;
for($g = 0; $g < sizeof($checkb); $g++) {
$checkb[$g] = $row['info'.$g.''];
}
}
}
?>

and last but not least, index.php

<?phpinclude('load.php');
?>


<form action="write.php" method="POST">
<input type=checkbox name='Check0' value=check0 <?php echo $checkb[0]?>>Check me!
<input type=checkbox name='Check1' value=check1 <?php echo $checkb[1]?>>Check me!
<input type=checkbox name='Check2' value=check1 <?php echo $checkb[2]?>>Check me!
<input type=submit value="ok"></form>

Mind you I am VERY new to php. It took me a few hours to figure this out, so be kind with your vocabulary please.


Basically, I know there's a way to only make it update columns that were changed. Something like if (check[$i] == $checkb[$i]), or along those lines. I don't really know how to do it efficiently.

As of right now, this all works perfectly. checkb is an array that is filled in config.php

//global variables$size = 3;$checkb = array();$checkb = array_fill(0, $size, '!checked');$isLogged = true;

I would also like to use isLogged to check if the IP is already in the DB, if it's not, use a different query. But I can't get that to work for some odd reason.

Thank you for your help guys!

 

[Negata]

Well first of all, you don't need to optimize it for performance. You might want to do it if you wanted to make sure to toggle only those values which were actually changed in order to not possibly overwrite another user's changes to other fields though.

Basically you already have the answer you're looking for. You just need to do the loading part before you attempt to save and compare the new values against the ones you just loaded. Don't mind efficiency on something like this that has a constant number of items. It'd be different if the array of items were of variable length and had the possibility to grow into tens of thousands of elements where only a few of them change each time. In that case the options would be rows in an options table anyway and not columns on a single row.

Third, before long you'll probably want to utilize some ORM library (instead of manually accessing/writing to table rows), but I get it you're just trying out stuff right now so look into abstractions like that when you get there.


As for tracking isLogged, that's data that needs to be stored in the DB as well. Each page load is its new fresh instance of the world which has no recollection of what's happened in the past.

 

[Syre]

Well first of all, you don't need to optimize it for performance. You might want to do it if you wanted to make sure to toggle only those values which were actually changed in order to not possibly overwrite another user's changes to other fields though.

Basically you already have the answer you're looking for. You just need to do the loading part before you attempt to save and compare the new values against the ones you just loaded. Don't mind efficiency on something like this that has a constant number of items. It'd be different if the array of items were of variable length and had the possibility to grow into tens of thousands of elements where only a few of them change each time. In that case the options would be rows in an options table anyway and not columns on a single row.

Third, before long you'll probably want to utilize some ORM library (instead of manually accessing/writing to table rows), but I get it you're just trying out stuff right now so look into abstractions like that when you get there.


As for tracking isLogged, that's data that needs to be stored in the DB as well. Each page load is its new fresh instance of the world which has no recollection of what's happened in the past.

I figured out the isLogged thing. The problem is it's updating 100 rows, right now, and I'm not even 10% into the game. you can check it out @ dq8.site.nfoservers.com

Anyways, here's my workaround for isLogged

$query3 = "SELECT * FROM `test` WHERE `ip` = '".$_SERVER['REMOTE_ADDR']."'";$result33 = mysqli_query($con, $query3);
if (mysqli_num_rows($result33) > 0) {
$isLogged = 1;
} else {
$isLogged = 0;
}

And then I added checks in load and write...
write

if ($isLogged == 0) {
$query2 = "INSERT INTO `test` (id, ip) VALUES (DEFAULT, '". $_SERVER['REMOTE_ADDR'] ."')";
if (!mysqli_query($con, $query2)) {
die('Error: '.mysqli_error($con));
} else {
$write = 1;
}
} else {
$write = 1;
}

load

if ($isLogged == 1) {
du stuff
} else {
echo 'Welcome! Enjoy the list!';
}

This is honestly the first project I have ever taken up with php.

How would I go about only updating the columns that changed?


After looking into ORM frameworks, I realize what you mean. It would definitely make all of this run a lot smoother, but I'm afraid that's a little bit past my ability. I can only understand so much of it. :/

 

[Negata]

I figured out the isLogged thing. The problem is it's updating 100 rows, right now, and I'm not even 10% into the game. you can check it out @ dq8.site.nfoservers.com

Well I don't know if it'll be a bottleneck there but as long as the number of checkboxes per row is constant (as opposed to growing in relation to stored data) it theoretically doesn't optimize significantly. Just make sure you don't repeat your code anywhere and it'll be ok to optimize it later if and only if it becomes a problem and you're sure it's exactly that. I doubt it will though. Then again the load-compare-write I mentioned shouldn't be too too hard to implement as an exercise even if it's of no great use.

 

[ThuGie]

<?phpinclude('config.php');
for ($i = 0;$i < sizeof($checkb);$i++) {
if (isset($_POST["Check".$i.""])){
$checkb[$i] = 'checked';
} else {
$checkb[$i] = '!checked';
}
}
for ($z = 0; $z < sizeof($checkb);$z++) {
$query = "UPDATE `test` SET info".$z." = '".$checkb[$z]."' WHERE ip = '" . $_SERVER['REMOTE_ADDR'] ."'";
if (!mysqli_query($con, $query)) {
	die('Error: ' . mysqli_error($con));
	}
	}
	echo "".sizeof($checkb)." records updated";	
?>

Change
$query = "UPDATE `test` SET info".$z." = '".$checkb[$z]."' WHERE ip = '" . $_SERVER['REMOTE_ADDR'] ."'";
to
$query = "UPDATE `test` SET info".$z." = '".$checkb[$z]."' WHERE ip = '" . $_SERVER['REMOTE_ADDR'] ."' LIMIT 1";

Should speed it up quite a bit .

 

[Negata]

$query = "UPDATE `test` SET info".$z." = '".$checkb[$z]."' WHERE ip = '" . $_SERVER['REMOTE_ADDR'] ."' LIMIT 1";

Should speed it up quite a bit .

... no.

 

[ThuGie]

Actually Yes..
in this case as he has hunderds of rows, mysql will check all rows if it needs to update it, even if it found it at row 1.
add a limit 1 once it finds one it will update it and stop searching.

 

[TimeBomb]

<?phpinclude('config.php');
for ($i = 0;$i < sizeof($checkb);$i++) {
if (isset($_POST["Check".$i.""])){
$checkb[$i] = 'checked';
} else {
$checkb[$i] = '!checked';
}
}
for ($z = 0; $z < sizeof($checkb);$z++) {
$query = "UPDATE `test` SET info".$z." = '".$checkb[$z]."' WHERE ip = '" . $_SERVER['REMOTE_ADDR'] ."'";
if (!mysqli_query($con, $query)) {
	die('Error: ' . mysqli_error($con));
	}
	}
	echo "".sizeof($checkb)." records updated";	
?>

Change
$query = "UPDATE `test` SET info".$z." = '".$checkb[$z]."' WHERE ip = '" . $_SERVER['REMOTE_ADDR'] ."'";
to
$query = "UPDATE `test` SET info".$z." = '".$checkb[$z]."' WHERE ip = '" . $_SERVER['REMOTE_ADDR'] ."' LIMIT 1";

Should speed it up quite a bit .

Good intuition. But in actuality, LIMIT works differently for update queries than it does for select queries, thus this doesn't do what you think it does.

Let's look at the documentation:

You must be registered to see links

The LIMIT clause places a limit on the number of rows that can be updated.

While the select query documentation states:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement.

So they work differently. Sticking a limit on an update query may be of use if, for example, you know you want to update only a single row. Then, if the query somehow tries to do something unexpected, only one row would be affected at max. Kind of a security net. But, in the end, limit will not increase performance of an update statement.

 

[ThuGie]

Well i am then from now on corrected on this, thank you .
I guess where i read up on this miss-informed me and been doing this for quite some years haha.
Though so far i know no real harm has been done, as i only used it when i actually needed to update 1 row etc.

 

[TimeBomb]

Well i am then from now on corrected on this, thank you .
I guess where i read up on this miss-informed me and been doing this for quite some years haha.
Though so far i know no real harm has been done, as i only used it when i actually needed to update 1 row etc.

If it makes you feel any better, prior to researching this while making my above post, while I never used LIMIT on update statements before, I didn't know exactly how they worked with update statements either. :

 

[ThuGie]

I felt better the moment you told me i was wrong, and actually provided proof haha.

 

[Negata]

Actually Yes..
in this case as he has hunderds of rows, mysql will check all rows if it needs to update it, even if it found it at row 1.
add a limit 1 once it finds one it will update it and stop searching.

Regardless of how it works, its theoretical significance is neglible. Even if the update ran a search through all entries in a table it'd have to do N operations (the number of rows) while on average if the update is cut short on the first match it'll have to search half-way to find it, reducing the number of operations to N/2. Now if this update is all your program ever does then you'll have improved the performance only by half, but that's not the case so while looking to find this kind of micro-optimizations (even if they do help some) you may be spending lots of time fine-tuning half a microsecond off an operation that probably isn't going to matter anyway among all the other time-consuming operations.

Now I'm not saying it's totally useless (no idea), but saying it's "quite a bit" of an improvement is a wild over statement...

By the way, be very careful about analyzing the performance by looking at best-case scenarios (where the match is always the first row in the table) because in reality this almost never happens. What you should do is look at the worst case scenarios and balance your efforts considering them, and if you look at how efficiency on many well-known algorithms is reported, you'll see the most commonly given information is indeed the worst case time consumption relative to data size, e.g. O(n^2).