tags:

views:

86

answers:

4
Q: 

Php / MySQL search function: there must be an easier way of achieving this? 

//lets get the auto name set
$accepted = 0;
$sql = mysql_query("SELECT * FROM ".TBL_FACTIONS." WHERE f_name='no_name'");
$numrows = mysql_num_rows($sql);
//default name 1
if($numrows == 0){ $auto_name = 'no_name'; $accepted = 1;}
//default name 2
if ($accepted == 0){ $sql = mysql_query("SELECT * FROM ".TBL_FACTIONS." 
WHERE f_name='no_name[02]'"); $numrows = mysql_num_rows($sql); 
if($numrows == 0){$auto_name = 'no_name[02]'; $accepted = 1;}     }
//default name 3
if ($accepted == 0){ $sql = mysql_query("SELECT * FROM ".TBL_FACTIONS." 
WHERE f_name='no_name[03]'"); $numrows = mysql_num_rows($sql); 
if($numrows == 0){$auto_name = 'no_name[03]'; $accepted = 1;}     }
//default name 4
if ($accepted == 0){ $sql = mysql_query("SELECT * FROM ".TBL_FACTIONS." 
WHERE f_name='no_name[04]'"); $numrows = mysql_num_rows($sql); 
if($numrows == 0){$auto_name = 'no_name[04]'; $accepted = 1;}     }
//default name 5
if ($accepted == 0){ $sql = mysql_query("SELECT * FROM ".TBL_FACTIONS." 
WHERE f_name='no_name[05]'"); $numrows = mysql_num_rows($sql); 
if($numrows == 0){$auto_name = 'no_name[05]'; $accepted = 1;}     }
//default name 6
if ($accepted == 0){ $sql = mysql_query("SELECT * FROM ".TBL_FACTIONS." 
WHERE f_name='no_name[06]'"); $numrows = mysql_num_rows($sql); 
if($numrows == 0){$auto_name = 'no_name[06]'; $accepted = 1;}     }

//-->update faction table
mysql_query("INSERT INTO ".TBL_FACTIONS." (f_id, f_name, f_status, 
f_boss, f_uboss, f_rhm, f_lhm, f_r1, f_r2, f_nat, 
f_funds, f_fmj, f_score,f_footer)
VALUES ('','$auto_name','0','$username','','','','','','gb','0','0','0','')");

This checks my MySQL database to create an automatic name for a created row in the table. It works fine like this, but i think there has to be an easier, more efficient, way of achieving the same output?

Also, how do I add a constant loop through so that it always creates a no_name[XX] insertion?

+1  A: 

Well you seem to be adding them iteratively so... can't you do this:

$sql = mysql_query("SELECT * FROM ".TBL_FACTIONS." WHERE f_name='no_name' OR f_name='no_name[02]' OR f_name='no_name[03]' OR f_name='no_name[04]' OR f_name='no_name[05]' OR f_name='no_name[06]'");

and then do

$num_rows = mysql_num_rows(mysql_query($sql));

then:

switch($num_rows){
    case 0:
    $auto_name = 'no_name';
    break;
    case 1:
    $auto_name = 'no_name[02]';
    break;
    case 2:
    $auto_name = 'no_name[03]';
    break;
    case 3:
    $auto_name = 'no_name[04]';
    break;
    case 4:
    $auto_name = 'no_name[05]';
    break;
    case 5:
    $auto_name = 'no_name[06]';
    break;
}
mysql_query("INSERT INTO ".TBL_FACTIONS." (f_id, f_name, f_status, f_boss, f_uboss, f_rhm, 
f_lhm, f_r1, f_r2, f_nat, f_funds, f_fmj, f_score,f_footer)
VALUES ('','$auto_name','0','$username','','','','','','gb','0','0','0','')");

Also, for the constant loop thing you are asking about you just need to do the same as above but without the switch loop... with a while loop... so:

$result = mysql_query("SELECT * FROM ".TBL_FACTIONS." WHERE f_name='no_name'");
$i = 0;
while(mysql_num_rows($result)){
    $i++;
    $result = mysql_query("SELECT * FROM ".TBL_FACTIONS." WHERE f_name='no_name[".$i."]'");
}
// $i should be the next number to add now...
if($i > 0){
    $sql = "INSERT INTO ".TBL_FACTIONS." (f_id, f_name, f_status, f_boss, f_uboss, f_rhm, 
f_lhm, f_r1, f_r2, f_nat, f_funds, f_fmj, f_score,f_footer)
VALUES ('','no_name[$i]','0','$username','','','','','','gb','0','0','0','')";
}
else {
    $sql = "INSERT INTO ".TBL_FACTIONS." (f_id, f_name, f_status, f_boss, f_uboss, f_rhm, 
f_lhm, f_r1, f_r2, f_nat, f_funds, f_fmj, f_score,f_footer)
VALUES ('','no_name','0','$username','','','','','','gb','0','0','0','')";
}
Thomas Clayson  2010-08-17 10:28:08
when i try this code, it justs inserts "no_name" over and over again (per submit) ?
Callum Johnson  2010-08-17 10:44:15
sorry - typo in the code... (i hadnt put in the []s after no_name in the query) plus - if you want to get the double numbering system you have [01] [02] [03] etc... then you'll have to format $i appropriately. :)
Thomas Clayson  2010-08-17 10:47:56
I've tried it again, but if no_name isn't there, the name is always no_name[0].
Callum Johnson  2010-08-17 11:03:06
ok... found the problem... if theres no "no_name" entry then it adds "no_name[0]" and still won't find "no_name" so... i've edited it... have a look and try it again. :)
Thomas Clayson  2010-08-17 11:16:47
yay! :D it works! Now to alter the double number thing aha. I'll probably add that alteration another day ^^. Thank you for your time and most importantly, thank you for providing a solution to my problem!
Callum Johnson  2010-08-17 11:26:56
edit: decided to add the double number thing already :)
Callum Johnson  2010-08-17 11:31:44
what if there are more than 6 names? The approach proposed by Haim Evgi is much better IMO, it will work no matter how many default names you have.
nico  2010-08-17 11:57:01
@nico: Thomas edited his code to allow for that?
Callum Johnson  2010-08-17 11:59:51
A: 

Use a loop. Any time there are numerous different things all doing the same only with a different index, use a loop. That way you don't have to add more code as more choices get added.

$default_names = 5; // set this dynamically if needed
$accepted = 0;
$sql = mysql_query("SELECT * FROM ".TBL_FACTIONS." WHERE f_name='no_name'");
$numrows = mysql_num_rows($sql);
if($numrows == 0) { 
    $auto_name = 'no_name'; $accepted = 1;
}
else
{
  for ($i = 0; $i < $default_names ; $i++)
  {
     $sql = mysql_query("SELECT * FROM ".TBL_FACTIONS." WHERE f_name='no_name['0'.$i + 2.']'"); 
     $numrows = mysql_num_rows($sql); 
     if($numrows == 0) {
       $auto_name = "no_name['0'.$i + 2.']"; 
       $accepted = 1;
       break;
     }
  }
}
mysql_query("INSERT INTO ".TBL_FACTIONS." (f_id, f_name, f_status, f_boss, f_uboss, f_rhm, 
f_lhm, f_r1, f_r2, f_nat, f_funds, f_fmj, f_score,f_footer)
VALUES ('','$auto_name','0','$username','','','','','','gb','0','0','0','')");
gabe3886  2010-08-17 10:29:59
I've tried your answer out and i get the following error:Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRINGoffending line: $auto_name = 'no_name[0'.$i + 2.']';
Callum Johnson  2010-08-17 10:34:13
$auto_name = 'no_name[0'.$i + 2.']'; -> $auto_name = 'no_name[0'.($i + 2).']'; (parenthesis should solve the error)
MartyIX  2010-08-17 10:37:42
still getting: Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING after change made
Callum Johnson  2010-08-17 10:42:55
it inserts the name "no_name", but then if that row exists, it doesn't insert another row.
Callum Johnson  2010-08-17 11:06:48
A: 

what i see that you want to find the max no_name that available ,
if so maybe do a SUBSTRING on this field to find this max, and insert to table max+1

something like :

SELECT 

 MAX(SUBSTRING_INDEX(SUBSTRING(f_name,8) , ']',1 ) ) AS max_id

 FROM ".TBL_FACTIONS."
Haim Evgi  2010-08-17 10:39:03
Your solution is much more elegant than the loop ones, but there are two errors. 1) you should include a WHERE statement otherwise it will also count non-default names, like "somename3". This is easily done with `WHERE f_name LIKE "no_name%"` 2) You should drop the 3rd parameter of substring (the 1) otherwise you won't get any default name with number > 10
nico  2010-08-17 11:25:19
@nico thanks , about 1 i agree because that i write something like bbecause i do not know what is in the db , about 2 is not an error look at http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_substring-index , is only the count occurrences of the delimiter
Haim Evgi  2010-08-17 11:36:01
@Haim Evgi: Sorry, you're right, I misread the code, I thought it was a parameter of `SUBSTRING`, instead it was the 3rd parameter of `SUBSTRING_INDEX`... :)
nico  2010-08-17 11:54:53
never mind he chooses thomas answer , my is probably not relevant
Haim Evgi  2010-08-17 12:06:33
@nico as a matter of fact, there should be no stupid `no_name` words at all, but just a number. Lack of database basics produce nightmares. in both question and answers.
Col. Shrapnel  2010-08-17 12:43:17
+1  A: 

First of all, you can remove the accepted check, by replacing it with the inverted check you're using to set the value, and the value used in the sql statement to the variable set in the if($numrows == 0) afterwards.

Remember, setting a variable again will overwrite it's previous value.

Also, I've put an if(TRUE) condititional around the first statement.

//lets get the auto name set
if (TRUE) {
  $auto_name = 'no_name';
  $sql = mysql_query("SELECT * FROM ".TBL_FACTIONS." WHERE f_name='".$auto_name."'");
  $numrows = mysql_num_rows($sql);
}

//default name 2
if ($numrows != 0) {
  $auto_name = 'no_name[02]';
  $sql = mysql_query("SELECT * FROM ".TBL_FACTIONS." WHERE f_name='".$auto_name."'");
  $numrows = mysql_num_rows($sql); 
}

//default name 3
if ($numrows != 0) {
  $auto_name = 'no_name[03]';
  $sql = mysql_query("SELECT * FROM ".TBL_FACTIONS." WHERE f_name='".$auto_name."'");
  $numrows = mysql_num_rows($sql);
}

//default name 4
if ($numrows != 0) {
  $auto_name = 'no_name[04]';
  $sql = mysql_query("SELECT * FROM ".TBL_FACTIONS." WHERE f_name='".$auto_name."'");
  $numrows = mysql_num_rows($sql); 
}

//default name 5
if ($numrows != 0) {
  $auto_name = 'no_name[05]';
  $sql = mysql_query("SELECT * FROM ".TBL_FACTIONS." WHERE f_name='".$auto_name."'");
  $numrows = mysql_num_rows($sql); 
}

//default name 6
if ($numrows != 0) {
  $auto_name = 'no_name[06]';
  $sql = mysql_query("SELECT * FROM ".TBL_FACTIONS." WHERE f_name='".$auto_name."'");
  $numrows = mysql_num_rows($sql); 
}

//-->update faction table
mysql_query("INSERT INTO ".TBL_FACTIONS." (f_id, f_name, f_status, f_boss, f_uboss, f_rhm, f_lhm, f_r1, f_r2, f_nat, f_funds, f_fmj, f_score,f_footer) VALUES ('','$auto_name','0','$username','','','','','','gb','0','0','0','')");

You might notice now that each block has the same code except for the $auto_name = 'no_name'; lines, which differ only in the numeric area, and the first block is always run, which makes it perfect for a do{}while() loop.

$numeric_part = ''; // numeric part is empty for the first time
$i = 1 // which name are we're checking

do {
  $auto_name = 'no_name'.$numeric_part;
  $sql = mysql_query("SELECT * FROM ".TBL_FACTIONS." WHERE f_name='".$auto_name."'");
  $numrows = mysql_num_rows($sql);

  //prepare for the next round trough the loop
  $i++;
  $numeric_part = '['.$i.']';
} while ($numrows != 0);

//-->update faction table
mysql_query("INSERT INTO ".TBL_FACTIONS." (f_id, f_name, f_status, f_boss, f_uboss, f_rhm, f_lhm, f_r1, f_r2, f_nat, f_funds, f_fmj, f_score,f_footer) VALUES ('','$auto_name','0','$username','','','','','','gb','0','0','0','')");

(note that there is a small difference in this code, it uses the, $noname[X] for entries 0-9, $noname[XX] for entries 10-99 and $noname[XXX] for entries 100-999 and so on, if you want this different, you need to change the $numeric_part = '['.$i.']'; line)

This is still not very efficient, as this still hits your database a total of 5000 times, to enter only 100 entries, and a total of 500000 (half a million) times to enter 1000 (1K) of entries, with the last query hitting your database 1000 (1K) times.

(your hosting/your computer WILL NOT LIKE THIS )

A more efficient way to continue would be to add entries simply to the end, and ignore empty entries in between, (which you are reusing now.)

Ignoring empty entries in between is simply done by allowing NULL entries for the f_name field, and replacing these on output with a value based on the f_id field.

(I'm leaving coding the last part as an exercise for you.)

alexanderpas  2010-08-17 12:08:25
thank you for this. Will I still need to ignore entries if say, the maximum number of records in by DB will be roughly 10?
Callum Johnson  2010-08-17 13:56:34
Ignoring the empty entries will **always** be faster, even 10 entries will do 50 hits to the database, and up to 10 hits/entry for each entry which'll be reusing a removed entry. a basic rule when re-using this code, is that it will do 1 hit on the database for each entry that is before an empty entry.
alexanderpas  2010-08-17 18:24:40

related questions

Remove Quotes and Commas from a String in MySQL
What's the best way of converting a mysql database to a sqlite one?
How do you manage databases in development, test, and production?
Multiple foreign keys?
Add 1 to a field (MySQL)
Issues using MS Access as a front-end to a MySQL database back-end?
Bigger than a char but smaller than a blob
How do I retrieve my MySQL username and password?
Long-time LAMP Developer moving to VB.NET
How do I Concatenate entire result sets in MySQL?
Full complete MySQL database replication? Ideas? What do people do?
SQL: Distribution of table in time
SQLite vs MySQL
How do I create a new Ruby on Rails application using MySQL instead of SQLite?
Multiple Updates in MySQL
MySQL/Apache Error in PHP MySQL query
What all do I need to escape when sending a (My)SQL query?
Auto Generate Database Diagram MySQL
Mechanisms for tracking DB schema changes
How big can a MySQL database get before performance starts to degrade.
Python and MySQL
SQL Server 2005 implementation of MySQL REPLACE INTO?
How to export data from SQL Server 2005 to MySQL
Throw Error In MySQL Trigger
Binary Data in MySQL