Jump to content

How to shorten this code

needs_upgrade

By needs_upgrade
in PHP Coding Help

 Share

Recommended Posts

needs_upgrade Regular Member

needs_upgrade

Posted
Posted

Hello guys.

 

How can I shorten this code? 

	// get the parent1 of bin_id and its details
	$sql = "SELECT bin_id, left_id, left_num, right_id, right_num 
			FROM binaries WHERE left_id = '$bin_id' OR right_id = '$bin_id'";
	$res1 = mysql_query($sql); $num1 = mysql_num_rows($res1); 
	if ($num1 > 0) {
		$row1 = mysql_fetch_array($res1); 
		if ($row1[1] == $bin_id) { // bin_id is left child of $row1[0]
			$sql = "UPDATE binaries SET left_num = $row1[2] + 1 WHERE bin_id = '$row1[0]' LIMIT 1";
			mysql_query($sql) or die(mysql_error()); 
		} elseif ($row1[3] == $bin_id) { // bin_id is right child of $row1[0]
			$sql = "UPDATE binaries SET right_num = $row1[4] + 1 WHERE bin_id = '$row1[0]' LIMIT 1";
			mysql_query($sql) or die(mysql_error()); 
		}
		// level 2
		$sql = "SELECT bin_id, left_id, left_num, right_id, right_num 
				FROM binaries WHERE left_id = '$row1[0]' OR right_id = '$row1[0]'";
		$res2 = mysql_query($sql); $num2 = mysql_num_rows($res2); 
		if ($num2 > 0) {
			$row2 = mysql_fetch_array($res2); 
			if ($row2[1] == $row1[0]) { // bin_id is left child of $row2[0]
				$sql = "UPDATE binaries SET left_num = $row2[2] + 1 WHERE bin_id = '$row2[0]' LIMIT 1";
				mysql_query($sql) or die(mysql_error()); 
			} elseif ($row2[3] == $row1[0]) { // bin_id is right child of $row2[0]
				$sql = "UPDATE binaries SET right_num = $row2[4] + 1 WHERE bin_id = '$row2[0]' LIMIT 1";
				mysql_query($sql) or die(mysql_error()); 
			}
			// level 3
			$sql = "SELECT bin_id, left_id, left_num, right_id, right_num 
					FROM binaries WHERE left_id = '$row2[0]' OR right_id = '$row2[0]'";
			$res3 = mysql_query($sql); $num3 = mysql_num_rows($res3); 
			if ($num3 > 0) {
				$row3 = mysql_fetch_array($res3); 
				if ($row3[1] == $row2[0]) { // bin_id is left child of $row3[0]
					$sql = "UPDATE binaries SET left_num = $row3[2] + 1 WHERE bin_id = '$row3[0]' LIMIT 1";
					mysql_query($sql) or die(mysql_error()); 
				} elseif ($row3[3] == $row2[0]) { // bin_id is right child of $row3[0]
					$sql = "UPDATE binaries SET right_num = $row3[4] + 1 WHERE bin_id = '$row3[0]' LIMIT 1";
					mysql_query($sql) or die(mysql_error()); 
				}
				// level 4
				$sql = "SELECT bin_id, left_id, left_num, right_id, right_num 
						FROM binaries WHERE left_id = '$row3[0]' OR right_id = '$row3[0]'";
				$res4 = mysql_query($sql); $num3 = mysql_num_rows($res3); 
				if ($num4 > 0) {
					$row4 = mysql_fetch_array($res4); 
					if ($row4[1] == $row3[0]) { // bin_id is left child of $row4[0]
						$sql = "UPDATE binaries SET left_num = $row4[2] + 1 WHERE bin_id = '$row4[0]' LIMIT 1";
						mysql_query($sql) or die(mysql_error()); 
					} elseif ($row4[3] == $row3[0]) { // bin_id is right child of $row4[0]
						$sql = "UPDATE binaries SET right_num = $row4[4] + 1 WHERE bin_id = '$row4[0]' LIMIT 1";
						mysql_query($sql) or die(mysql_error()); 
					}
					// level 5
					$sql = "SELECT bin_id, left_id, left_num, right_id, right_num 
							FROM binaries WHERE left_id = '$row4[0]' OR right_id = '$row4[0]'";
					$res5 = mysql_query($sql); $num5 = mysql_num_rows($res5); 
					if ($num5 > 0) {
						$row5 = mysql_fetch_array($res5); 
						if ($row5[1] == $row4[0]) { // bin_id is left child of $row5[0]
							$sql = "UPDATE binaries SET left_num = $row5[2] + 1 WHERE bin_id = '$row5[0]' LIMIT 1";
							mysql_query($sql) or die(mysql_error()); 
						} elseif ($row5[3] == $row4[0]) { // bin_id is right child of $row5[0]
							$sql = "UPDATE binaries SET right_num = $row5[4] + 1 WHERE bin_id = '$row5[0]' LIMIT 1";
							mysql_query($sql) or die(mysql_error()); 
						}
						// level 6
						$sql = "SELECT bin_id, left_id, left_num, right_id, right_num 
								FROM binaries WHERE left_id = '$row5[0]' OR right_id = '$row5[0]'";
						$res6 = mysql_query($sql); $num6 = mysql_num_rows($res6); 
						if ($num6 > 0) {
							$row6 = mysql_fetch_array($res6); 
							if ($row6[1] == $row5[0]) { // bin_id is left child of $row6[0]
								$sql = "UPDATE binaries SET left_num = $row6[2] + 1 WHERE bin_id = '$row6[0]' LIMIT 1";
								mysql_query($sql) or die(mysql_error()); 
							} elseif ($row6[3] == $row5[0]) { // bin_id is right child of $row6[0]
								$sql = "UPDATE binaries SET right_num = $row6[4] + 1 WHERE bin_id = '$row6[0]' LIMIT 1";
								mysql_query($sql) or die(mysql_error()); 
							}
							// level 7
							$sql = "SELECT bin_id, left_id, left_num, right_id, right_num 
									FROM binaries WHERE left_id = '$row6[0]' OR right_id = '$row6[0]'";
							$res7 = mysql_query($sql); $num7 = mysql_num_rows($res7); 
							if ($num7 > 0) {
								$row7 = mysql_fetch_array($res7); 
								if ($row7[1] == $row6[0]) { // bin_id is left child of $row7[0]
									$sql = "UPDATE binaries SET left_num = $row7[2] + 1 WHERE bin_id = '$row7[0]' LIMIT 1";
									mysql_query($sql) or die(mysql_error()); 
								} elseif ($row7[3] == $row6[0]) { // bin_id is right child of $row7[0]
									$sql = "UPDATE binaries SET right_num = $row7[4] + 1 WHERE bin_id = '$row7[0]' LIMIT 1";
									mysql_query($sql) or die(mysql_error()); 
								}
								// level 8
								$sql = "SELECT bin_id, left_id, left_num, right_id, right_num 
										FROM binaries WHERE left_id = '$row7[0]' OR right_id = '$row7[0]'";
								$res8 = mysql_query($sql); $num8 = mysql_num_rows($res8); 
								if ($num8 > 0) {
									$row8 = mysql_fetch_array($res8); 
									if ($row8[1] == $row7[0]) { // bin_id is left child of $row8[0]
										$sql = "UPDATE binaries SET left_num = $row8[2] + 1 WHERE bin_id = '$row8[0]' LIMIT 1";
										mysql_query($sql) or die(mysql_error()); 
									} elseif ($row8[3] == $row7[0]) { // bin_id is right child of $row8[0]
										$sql = "UPDATE binaries SET right_num = $row8[4] + 1 WHERE bin_id = '$row8[0]' LIMIT 1";
										mysql_query($sql) or die(mysql_error()); 
									}
									// level 9
									$sql = "SELECT bin_id, left_id, left_num, right_id, right_num 
											FROM binaries WHERE left_id = '$row8[0]' OR right_id = '$row8[0]'";
									$res9 = mysql_query($sql); $num9 = mysql_num_rows($res9); 
									if ($num9 > 0) {
										$row9 = mysql_fetch_array($res9); 
										if ($row9[1] == $row8[0]) { // bin_id is left child of $row9[0]
											$sql = "UPDATE binaries SET left_num = $row9[2] + 1 WHERE bin_id = '$row9[0]' LIMIT 1";
											mysql_query($sql) or die(mysql_error()); 
										} elseif ($row9[3] == $row8[0]) { // bin_id is right child of $row9[0]
											$sql = "UPDATE binaries SET right_num = $row9[4] + 1 WHERE bin_id = '$row9[0]' LIMIT 1";
											mysql_query($sql) or die(mysql_error()); 
										}
										// level 10
										$sql = "SELECT bin_id, left_id, left_num, right_id, right_num 
												FROM binaries WHERE left_id = '$row9[0]' OR right_id = '$row9[0]'";
										$res10 = mysql_query($sql); $num10 = mysql_num_rows($res10); 
										if ($num10 > 0) {
											$row10 = mysql_fetch_array($res10); 
											if ($row10[1] == $row9[0]) { // bin_id is left child of $row10[0]
												$sql = "UPDATE binaries SET left_num = $row10[2] + 1 WHERE bin_id = '$row10[0]' LIMIT 1";
												mysql_query($sql) or die(mysql_error()); 
											} elseif ($row10[3] == $row9[0]) { // bin_id is right child of $row10[0]
												$sql = "UPDATE binaries SET right_num = $row10[4] + 1 WHERE bin_id = '$row10[0]' LIMIT 1";
												mysql_query($sql) or die(mysql_error()); 
											}
											// level 11
											$sql = "SELECT bin_id, left_id, left_num, right_id, right_num 
													FROM binaries WHERE left_id = '$row10[0]' OR right_id = '$row10[0]'";
											$res11 = mysql_query($sql); $num11 = mysql_num_rows($res11); 
											if ($num11 > 0) {
												$row11 = mysql_fetch_array($res11); 
												if ($row11[1] == $row10[0]) { // bin_id is left child of $row11[0]
													$sql = "UPDATE binaries SET left_num = $row11[2] + 1 WHERE bin_id = '$row11[0]' LIMIT 1";
													mysql_query($sql) or die(mysql_error()); 
												} elseif ($row11[3] == $row10[0]) { // bin_id is right child of $row11[0]
													$sql = "UPDATE binaries SET right_num = $row11[4] + 1 WHERE bin_id = '$row11[0]' LIMIT 1";
													mysql_query($sql) or die(mysql_error()); 
												}
												// level 12
												$sql = "SELECT bin_id, left_id, left_num, right_id, right_num 
														FROM binaries WHERE left_id = '$row11[0]' OR right_id = '$row11[0]'";
												$res12 = mysql_query($sql); $num12 = mysql_num_rows($res12); 
												if ($num12 > 0) {
													$row12 = mysql_fetch_array($res12); 
													if ($row12[1] == $row11[0]) { // bin_id is left child of $row12[0]
														$sql = "UPDATE binaries SET left_num = $row12[2] + 1 WHERE bin_id = '$row12[0]' LIMIT 1";
														mysql_query($sql) or die(mysql_error()); 
													} elseif ($row12[3] == $row11[0]) { // bin_id is right child of $row12[0]
														$sql = "UPDATE binaries SET right_num = $row12[4] + 1 WHERE bin_id = '$row12[0]' LIMIT 1";
														mysql_query($sql) or die(mysql_error()); 
													}
													// level 13
													$sql = "SELECT bin_id, left_id, left_num, right_id, right_num 
															FROM binaries WHERE left_id = '$row12[0]' OR right_id = '$row12[0]'";
													$res13 = mysql_query($sql); $num13 = mysql_num_rows($res13); 
													if ($num13 > 0) {
														$row13 = mysql_fetch_array($res13); 
														if ($row13[1] == $row12[0]) { // bin_id is left child of $row13[0]
															$sql = "UPDATE binaries SET left_num = $row13[2] + 1 WHERE bin_id = '$row13[0]' LIMIT 1";
															mysql_query($sql) or die(mysql_error()); 
														} elseif ($row13[3] == $row12[0]) { // bin_id is right child of $row13[0]
															$sql = "UPDATE binaries SET right_num = $row13[4] + 1 WHERE bin_id = '$row13[0]' LIMIT 1";
															mysql_query($sql) or die(mysql_error()); 
														}
														// level 14
														$sql = "SELECT bin_id, left_id, left_num, right_id, right_num 
																FROM binaries WHERE left_id = '$row13[0]' OR right_id = '$row13[0]'";
														$res14 = mysql_query($sql); $num14 = mysql_num_rows($res14); 
														if ($num14 > 0) {
															$row14 = mysql_fetch_array($res14); 
															if ($row14[1] == $row13[0]) { // bin_id is left child of $row14[0]
																$sql = "UPDATE binaries SET left_num = $row14[2] + 1 WHERE bin_id = '$row14[0]' LIMIT 1";
																mysql_query($sql) or die(mysql_error()); 
															} elseif ($row14[3] == $row13[0]) { // bin_id is right child of $row14[0]
																$sql = "UPDATE binaries SET right_num = $row14[4] + 1 WHERE bin_id = '$row14[0]' LIMIT 1";
																mysql_query($sql) or die(mysql_error()); 
															}
															// level 15
															$sql = "SELECT bin_id, left_id, left_num, right_id, right_num 
																	FROM binaries WHERE left_id = '$row14[0]' OR right_id = '$row14[0]'";
															$res15 = mysql_query($sql); $num15 = mysql_num_rows($res15); 
															if ($num15 > 0) {
																$row15 = mysql_fetch_array($res15); 
																if ($row15[1] == $row14[0]) { // bin_id is left child of $row15[0]
																	$sql = "UPDATE binaries SET left_num = $row15[2] + 1 WHERE bin_id = '$row15[0]' LIMIT 1";
																	mysql_query($sql) or die(mysql_error()); 
																} elseif ($row15[3] == $row14[0]) { // bin_id is right child of $row15[0]
																	$sql = "UPDATE binaries SET right_num = $row15[4] + 1 WHERE bin_id = '$row15[0]' LIMIT 1";
																	mysql_query($sql) or die(mysql_error()); 
																}
																// level 16
																$sql = "SELECT bin_id, left_id, left_num, right_id, right_num 
																		FROM binaries WHERE left_id = '$row15[0]' OR right_id = '$row15[0]'";
																$res16 = mysql_query($sql); $num16 = mysql_num_rows($res16); 
																if ($num16 > 0) {
																	$row16 = mysql_fetch_array($res16); 
																	if ($row16[1] == $row15[0]) { // bin_id is left child of $row16[0]
																		$sql = "UPDATE binaries SET left_num = $row16[2] + 1 WHERE bin_id = '$row16[0]' LIMIT 1";
																		mysql_query($sql) or die(mysql_error()); 
																	} elseif ($row16[3] == $row15[0]) { // bin_id is right child of $row16[0]
																		$sql = "UPDATE binaries SET right_num = $row16[4] + 1 WHERE bin_id = '$row16[0]' LIMIT 1";
																		mysql_query($sql) or die(mysql_error()); 
																	}
																	// level 17
																	$sql = "SELECT bin_id, left_id, left_num, right_id, right_num 
																			FROM binaries WHERE left_id = '$row16[0]' OR right_id = '$row16[0]'";
																	$res17 = mysql_query($sql); $num17 = mysql_num_rows($res17); 
																	if ($num17 > 0) {
																		$row17 = mysql_fetch_array($res17); 
																		if ($row17[1] == $row16[0]) { // bin_id is left child of $row17[0]
																			$sql = "UPDATE binaries SET left_num = $row17[2] + 1 WHERE bin_id = '$row17[0]' LIMIT 1";
																			mysql_query($sql) or die(mysql_error()); 
																		} elseif ($row17[3] == $row16[0]) { // bin_id is right child of $row17[0]
																			$sql = "UPDATE binaries SET right_num = $row17[4] + 1 WHERE bin_id = '$row17[0]' LIMIT 1";
																			mysql_query($sql) or die(mysql_error()); 
																		}
																		// level 18
																		$sql = "SELECT bin_id, left_id, left_num, right_id, right_num 
																				FROM binaries WHERE left_id = '$row17[0]' OR right_id = '$row17[0]'";
																		$res18 = mysql_query($sql); $num18 = mysql_num_rows($res18); 
																		if ($num18 > 0) {
																			$row18 = mysql_fetch_array($res18); 
																			if ($row18[1] == $row17[0]) { // bin_id is left child of $row18[0]
																				$sql = "UPDATE binaries SET left_num = $row18[2] + 1 WHERE bin_id = '$row18[0]' LIMIT 1";
																				mysql_query($sql) or die(mysql_error()); 
																			} elseif ($row18[3] == $row17[0]) { // bin_id is right child of $row18[0]
																				$sql = "UPDATE binaries SET right_num = $row18[4] + 1 WHERE bin_id = '$row18[0]' LIMIT 1";
																				mysql_query($sql) or die(mysql_error()); 
																			}
																			// level 19
																			$sql = "SELECT bin_id, left_id, left_num, right_id, right_num 
																					FROM binaries WHERE left_id = '$row18[0]' OR right_id = '$row18[0]'";
																			$res19 = mysql_query($sql); $num19 = mysql_num_rows($res19); 
																			if ($num19 > 0) {
																				$row19 = mysql_fetch_array($res19); 
																				if ($row19[1] == $row18[0]) { // bin_id is left child of $row19[0]
																					$sql = "UPDATE binaries SET left_num = $row19[2] + 1 WHERE bin_id = '$row19[0]' LIMIT 1";
																					mysql_query($sql) or die(mysql_error()); 
																				} elseif ($row19[3] == $row18[0]) { // bin_id is right child of $row19[0]
																					$sql = "UPDATE binaries SET right_num = $row19[4] + 1 WHERE bin_id = '$row19[0]' LIMIT 1";
																					mysql_query($sql) or die(mysql_error()); 
																				}
																				// level 20
																				$sql = "SELECT bin_id, left_id, left_num, right_id, right_num 
																						FROM binaries WHERE left_id = '$row19[0]' OR right_id = '$row19[0]'";
																				$res20 = mysql_query($sql); $num20 = mysql_num_rows($res20); 
																				if ($num20 > 0) {
																					$row20 = mysql_fetch_array($res20); 
																					if ($row20[1] == $row19[0]) { // bin_id is left child of $row20[0]
																						$sql = "UPDATE binaries SET left_num = $row20[2] + 1 WHERE bin_id = '$row20[0]' LIMIT 1";
																						mysql_query($sql) or die(mysql_error()); 
																					} elseif ($row20[3] == $row19[0]) { // bin_id is right child of $row20[0]
																						$sql = "UPDATE binaries SET right_num = $row20[4] + 1 WHERE bin_id = '$row20[0]' LIMIT 1";
																						mysql_query($sql) or die(mysql_error()); 
																					}
																					// level 21
																				}
																			}
																		}
																	}
																}
															}
														}
													}
												}
											}
										}
									}
								}
							}
						}
					}
				}
			}
		}
	}
Link to comment
Share on other sites
mac_gyver Prolific Member

mac_gyver

Posted
Posted

i don't recall what that structure is called, but if someone knows the name of it, i would bet that someone has solved this before and a search would turn up a solution.

 

i would retrieve ALL the data into an array, manipulate the data in the array (using a recursive function), then write all the data back into the database table using a multi value REPLACE query.

Link to comment
Share on other sites
DavidAM Member

DavidAM

Posted
Posted

I'm not sure what it is called either, but it looks like the same code over and over and over ...

 

First, use mysql_fetch_assoc instead of mysql_fetch_array(). Then I don't have to keep looking back at the query to see which column $row124[3] is referring to.

 

Second, how about a recursive function? (man I love these things)

// Recursive function returns when no rows are found for the Bin ID
function updateBinID($psBinID) {
	$sql = "SELECT bin_id, left_id, left_num, right_id, right_num 
			FROM binaries WHERE left_id = '$psBinID' OR right_id = '$psBinID'";
	$res1 = mysql_query($sql); 
	$num1 = mysql_num_rows($res1); 
	if ($num1 > 0) {
		$row1 = mysql_fetch_assoc($res1); 
		if ($row1['left_id'] == $bin_id) { // bin_id is left child of $row1[0]
			$sql = "UPDATE binaries SET left_num = {$row1['left_num']} + 1 WHERE bin_id = '{$row1['bin_id']}' LIMIT 1";
			mysql_query($sql) or die(mysql_error()); 
		} elseif ($row1['right_id'] == $bin_id) { // bin_id is right child of $row1[0]
			$sql = "UPDATE binaries SET right_num = {$row1['right_num']} + 1 WHERE bin_id = '{$row1[bin_id]}' LIMIT 1";
			mysql_query($sql) or die(mysql_error()); 
		}
		// Oops, almost forgot. Let's free the query resource so we don't overflow the stack
		mysql_free_result($res1);
		
		// level 2
		updateBinID($row1['bin_id']);
	}
}
* Not tested

 

As in your code, this only processes the FIRST row of the query. If there might be multiple rows, you need to inject a while loop inside the IF ($rows1) part.

 

Also, there's no real reason now to keep the "1" on all the variable names, I just got lazy.

 

Of course, since the mysql extension is deprecated, this should be converted to mysqli. And as long as you're doing that, we should use prepared statements for the SELECT and the UPDATE, that should speed it up a bit.

 

My guess is that Barand will come along with a nice neat SQL-only solution that makes all of my hard work here mute. But hey, live and learn. (yes, that's a challenge).

Link to comment
Share on other sites
Psycho Prolific Member

Psycho

Posted
Posted (edited)

I'm not understanding why we need to run a select query and THEN an update query. The select query is pulling all the records where the left or right id's match a particular value. Then, it increments the left or right values by one based upon the left or right id that matched. All of that logic can be done with a single query (at least for a single level). Just like in PHP you can create a condition statement and a true is interpreted as 1 and false as 0.

 

This will replace the SELECT statement and the two associative UPDATE queries ,as well as the logic that goes with them.

UPDATE binaries

SET left_num  = left_num + (left_id = '$psBinID'),
    right_num = right_num + (left_id = '$psBinID')

WHERE left_id = '$psBinID' OR right_id = '$psBinID'

Hmm . . . I guess you'd still have to do a SELECT query afterwards in order to get the ID of the records updated in order to get the child records though.

Edited by Psycho
Link to comment
Share on other sites
DavidAM Member

DavidAM

Posted
Posted

Yea, I thought about that. It was the child records that caused me to ignore it and besides, he's setting the child's NUM to the parent's NUM + 1, and we really don't know what this value represents. We could replace the if {...} else {...} with that single-query logic (or similar), which might save a little time in the long run.

 

In retrospect, if I really needed to do this, with an unknown number of levels, I would build a Stored Procedure to handle the looping update. Then it could be done with a single call to the database.

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.