Page 1 of 1

How do you use mysql? Is there a better way?

Posted: Mon Apr 11, 2005 9:35 am
by siefkencp
Just thought I would pose the question. I am doing alot of large database updates, inserts, ect... and I am doing alot of my work with while loops and if then statements which works but at this point I believe there must be a better way.

This is what I mean:

Code: Select all

<?
	
$tag_data = mysql_query("SELECT * FROM `XXXXX` WHERE `PEOPLE_CODE_ID` = '$idnum'")
	or die ("Not Found");
	
	$tag_array = mysql_fetch_array($tag_data, MYSQL_ASSOC);
	
	$power_id = $tag_array['PEOPLE_CODE_ID'];
	$lname = $tag_array['LAST_NAME'];
	$fname = $tag_array['FIRST_NAME'];
	$year = $tag_array['ACADEMIC_YEAR'];
	$app_stat = $tag_array['APP_STATUS'];
	$email_value = $tag_array['EMAIL_ADDRESS'];
	
	print "<b>$power_id</b> <br>\n";
	print "$lname, $fname -- $year <br>\n";
	print "$app_stat";

	$sent_data = mysql_query("SELECT * FROM `XXXXX` WHERE `PEOPLE_CODE_ID` = '$idnum'")
	or die("didnt find");
		$sent_array = mysql_fetch_array($sent_data, MYSQL_ASSOC);
		$contact_date = $sent_array['contact_date'];
		$email = $sent_array['email'];
		$snail_mail = $sent_array['snail_mail'];
		$application = $sent_array['application'];
		$openhouse = $sent_array['openhouse'];
		$sports = $sent_array['sports'];
		$equestrian = $sent_array['equestrian'];
		$schol_fa = $sent_array['schol_fa'];
		$aa = $sent_array['aa'];
		$general = $sent_array['general'];
		$choir = $sent_array['choir'];
		$pipe_band = $sent_array['pipe_band'];
		$personal_contact = $sent_array['personal_contact'];
		$comment = $sent_array['comment'];
		$has_visited = $sent_array['has_visited'];
		$follow_up = $sent_array['follow_up'];
		$intent_index = $sent_array['intent'];
			if ($intent_index == '1'){
			$intent = "Definitely";
			}
			if ($intent_index == '2'){
			$intent = "Maybe";
			}
			if ($intent_index == '3'){
			$intent = "Not";
			}
			if ($intent_index == '0'){
			$intent = "Not Defined";
			}
if ($contact_date == '') {
print "<br><br><b>Not yet called.</b>";
}
if ($contact_date != ''){

//display logic
?>
Yes I do use OO regularly...

Thanks for your 2 cents...

Posted: Mon Apr 11, 2005 9:37 am
by siefkencp
While loop example.

Code: Select all

while ($final_array = mysql_fetch_array($query_array, MYSQL_ASSOC)){
		
		print "
		<tr>
		<td>$filename</td>
		<td>$date_log</td>
		</tr>";

		$filename = $final_array['file'];
		$date_log = $final_array['date'];
		}
As you can see this stuff gets cumbersome after a while.

Posted: Mon Apr 11, 2005 9:53 am
by Bennettman
What you can do, is try to do all the SQL stuff at one time. For example

Code: Select all

<?php

// inserting data
// say you've got an array with all the INSERT command lines
$query = "";
for ($i = 0; $lines[$i]; $i++) {
     $query .= $lines[$i];
     if ($lines[$i + 1]) $query .= ";\n";
}
// next line inserts all the data at once
mysql_query($query) or die('failed doing that shizzle');


// parsing data
function get_sql($query) {
     if ($result = mysql_query($query)) {
          while ($rows[] = mysql_fetch_array($result, MYSQL_ASSOC)) {}
          return $rows;
     }
     else return FALSE;
}
// next line puts all the results into a 2-dim array
$final_array = get_sql($query_array);
foreach ($final_array as $row) {
     print "blah blah blah";
}

?>
The get_sql() function could be improved to distinguish between single-dimension and two-dimension arrays, etc, but that's the basic idea.

Posted: Mon Apr 11, 2005 9:59 am
by CoderGoblin
The key to database usage is to use a good database structure design. You need to use SQL joins to get only the data you want letting the DB do the work rather than Php.

Code: Select all

SELECT a,b,c FROM table_a, table_b WHERE tablea.code_id=1 and tablea.code_id=tableb.code_id
See also LEFT OUTER joins and the like...

I find the use of mysql_fetch_assoc far better than mysql_fetch_array. (Same result as mysql_fetch_array($query_array, MYSQL_ASSOC).

Code: Select all

while ($row = mysql_fetch_assoc($result) {
  echo('<tr>
        <td>'.$row['filename'].'</td>
        <td>'.$row['date_log'].'</td>
        </tr>');
}
Just a couple of pointers. I am sure others will give you more.

Posted: Mon Apr 11, 2005 11:43 am
by Chris Corbyn
I write a class (which perhaps takes a while to write depending upon the DB structure) but once I have that down I can play with my database really easily :wink: