Some php/mysql help

  • DavyDuke17
  • Graduate
  • Graduate
  • DavyDuke17
  • Posts: 166

Post 3+ Months Ago

Ok, I have no clue why this doesn't work, so any help is greatly appreciated. This searches my database through multiple tables and is supposed to stop when it finds a match with the name of the player they search for.

Code: [ Select ]
<?
function db_connect(){
  $hostname = "*****";
  $username = "*****";
  $password = "******";
  $dbName = "******";
  
  MYSQL_CONNECT($hostname, $username, $password) OR DIE("DB connection unavailable");
  @mysql_select_db( "$dbName") or die( "Unable to select database");
}

function search_db($usertable, $search){
  $metode = "Player";
  $sql = 'select * from ' . $usertable . ' where ' . $metode . ' like "%' . $search . '%" limit 0, 30';
  $query = mysql_query($sql) OR DIE(mysql_error());
  echo $sql;
  if ($row = mysql_fetch_array($query))
{
$variable1=$row["Player"];
$variable2=$row["Pos"];
$variable3=$row["HR"];
$variable4=$row["RBI"];
$variable5=$row["DOB"];
$sentinal=1;
} else {
   $sentinal=0;
  }
}

db_connect();
search_db('Yankees', $_POST['search']);
if ($sentinal==0) {
search_db('Anaheim', $_POST['search']);}
if ($sentinal==0) {
search_db('Arizona', $_POST['search']);}

mysql_close();
?>
  1. <?
  2. function db_connect(){
  3.   $hostname = "*****";
  4.   $username = "*****";
  5.   $password = "******";
  6.   $dbName = "******";
  7.   
  8.   MYSQL_CONNECT($hostname, $username, $password) OR DIE("DB connection unavailable");
  9.   @mysql_select_db( "$dbName") or die( "Unable to select database");
  10. }
  11. function search_db($usertable, $search){
  12.   $metode = "Player";
  13.   $sql = 'select * from ' . $usertable . ' where ' . $metode . ' like "%' . $search . '%" limit 0, 30';
  14.   $query = mysql_query($sql) OR DIE(mysql_error());
  15.   echo $sql;
  16.   if ($row = mysql_fetch_array($query))
  17. {
  18. $variable1=$row["Player"];
  19. $variable2=$row["Pos"];
  20. $variable3=$row["HR"];
  21. $variable4=$row["RBI"];
  22. $variable5=$row["DOB"];
  23. $sentinal=1;
  24. } else {
  25.    $sentinal=0;
  26.   }
  27. }
  28. db_connect();
  29. search_db('Yankees', $_POST['search']);
  30. if ($sentinal==0) {
  31. search_db('Anaheim', $_POST['search']);}
  32. if ($sentinal==0) {
  33. search_db('Arizona', $_POST['search']);}
  34. mysql_close();
  35. ?>


When I try this it doesn't work and the echo $sql outputs: select * from Yankees where Player like "%SearchTerm%" limit 0, 30select * from Anaheim where Player like "%Searchterm%" limit 0, 30
The sentinal is to stop it from further searching the tables, there is more down the list, but that is irrelevant. Thanks for any help.
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • gsv2com
  • Professor
  • Professor
  • User avatar
  • Posts: 776
  • Loc: Nippon

Post 3+ Months Ago

The variable $sentinal is not being returned. An easy way around this would be to put this at the top of the function:

Code: [ Select ]
global $sentinal;


Not the best way to do things, but at least you'll get your sentinal value returned. Also, try changing this:

Code: [ Select ]
if ($row = mysql_fetch_array($query))
{
$variable1=$row["Player"];
$variable2=$row["Pos"];
$variable3=$row["HR"];
$variable4=$row["RBI"];
$variable5=$row["DOB"];
$sentinal=1;
} else {
   $sentinal=0;
  }
}
  1. if ($row = mysql_fetch_array($query))
  2. {
  3. $variable1=$row["Player"];
  4. $variable2=$row["Pos"];
  5. $variable3=$row["HR"];
  6. $variable4=$row["RBI"];
  7. $variable5=$row["DOB"];
  8. $sentinal=1;
  9. } else {
  10.    $sentinal=0;
  11.   }
  12. }


into this:

Code: [ Select ]
if(mysql_num_rows($query)>0){
   while($row=mysql_fetch_array($query)){
     $player=$row['player'];
     $pos=$row['pos'];
     $hr=$row['hr'];
     $rbi=$row['rbi'];
     $dob=$row['dob'];
     $sentinal=1;
   }
} else {
   $sentinal=0;
}
  1. if(mysql_num_rows($query)>0){
  2.    while($row=mysql_fetch_array($query)){
  3.      $player=$row['player'];
  4.      $pos=$row['pos'];
  5.      $hr=$row['hr'];
  6.      $rbi=$row['rbi'];
  7.      $dob=$row['dob'];
  8.      $sentinal=1;
  9.    }
  10. } else {
  11.    $sentinal=0;
  12. }


You can also change this:

Code: [ Select ]
search_db('Yankees', $_POST['search']);
if ($sentinal==0) {
search_db('Anaheim', $_POST['search']);}
if ($sentinal==0) {
search_db('Arizona', $_POST['search']);}
  1. search_db('Yankees', $_POST['search']);
  2. if ($sentinal==0) {
  3. search_db('Anaheim', $_POST['search']);}
  4. if ($sentinal==0) {
  5. search_db('Arizona', $_POST['search']);}


into this:

Code: [ Select ]
search_db('Yankees', $_POST['search']);
if ($sentinal==0) search_db('Anaheim', $_POST['search']);
if ($sentinal==0) search_db('Arizona', $_POST['search']);
  1. search_db('Yankees', $_POST['search']);
  2. if ($sentinal==0) search_db('Anaheim', $_POST['search']);
  3. if ($sentinal==0) search_db('Arizona', $_POST['search']);


mysql_close() is also not necessary since it automatically does anyway after a page is finished loading.
  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Posts: 3245
  • Loc: South Africa

Post 3+ Months Ago

another way to do it is to have search_db return the value
Code: [ Select ]
return $sentinal
at the end of the function then assign the returned variable to another variable
Code: [ Select ]
$sentinalState = search_db
and do your test on $sentinal state
  • gsv2com
  • Professor
  • Professor
  • User avatar
  • Posts: 776
  • Loc: Nippon

Post 3+ Months Ago

Rabid Dog's method would probably be the preferred approach, but I wasn't sure if davyduke would know what to do with the information, thus the global recommendation.

Good info though, Rabid. :)
  • DavyDuke17
  • Graduate
  • Graduate
  • DavyDuke17
  • Posts: 166

Post 3+ Months Ago

I'd like to start off by thanking you guys so much. I went with the return style because I've done that before with C++, the only language I really know besides html. Anyways, it is working good now, the sentinal stops the search on the correct table. However, the variables in the
Code: [ Select ]
if(mysql_num_rows($query)>0){
   while($row=mysql_fetch_array($query)){
$variable1=$row["Player"];
$variable2=$row["Pos"];
$variable3=$row["HR"];
$variable4=$row["RBI"];
  1. if(mysql_num_rows($query)>0){
  2.    while($row=mysql_fetch_array($query)){
  3. $variable1=$row["Player"];
  4. $variable2=$row["Pos"];
  5. $variable3=$row["HR"];
  6. $variable4=$row["RBI"];

are now in the function and need to be returned. I'm guessing it would be easier to declare these variables global above, right? Because I don't know how you could tell assign them to a variable like the sentinal.
  • gsv2com
  • Professor
  • Professor
  • User avatar
  • Posts: 776
  • Loc: Nippon

Post 3+ Months Ago

I think it'd be better to just output the data using the function than to use global. You're going to want to output them anyway (most likely), so you might as well just build that part into the function.

But global would work.
  • Carnix
  • Guru
  • Guru
  • User avatar
  • Posts: 1098

Post 3+ Months Ago

It's almost always best to avoid global variables whenever possible. Globals like to hang around in memory longer...

I think it would be best to just have the function print the data out before it returns. just replace $variableX = with echo or print (I prefer print cause I'm an old Perl junky... heh) and some HTML formatting.

.c
  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Posts: 3245
  • Loc: South Africa

Post 3+ Months Ago

Carnix wrote:
It's almost always best to avoid global variables whenever possible. Globals like to hang around in memory longer...
.c


Agreed, the way I usually do it if I need to output multiple variables is to insert them into an array.

Code: [ Select ]
if(mysql_num_rows($query)>0){
   while($row=mysql_fetch_array($query)){
$variable1=$row["Player"];
$variable2=$row["Pos"];
$variable3=$row["HR"];
$variable4=$row["RBI"];
}

Would become
if(mysql_num_rows($query)>0){
   $i = 0;
   while($row=mysql_fetch_array($query)){
$varArray[$i][0]=$row["Player"];
$varArray[$i][1]=$row["Pos"];
$varArray[$i][2]=$row["HR"];
$varArray[$i][3]=$row["RBI"];
}

return $varArray;
  1. if(mysql_num_rows($query)>0){
  2.    while($row=mysql_fetch_array($query)){
  3. $variable1=$row["Player"];
  4. $variable2=$row["Pos"];
  5. $variable3=$row["HR"];
  6. $variable4=$row["RBI"];
  7. }
  8. Would become
  9. if(mysql_num_rows($query)>0){
  10.    $i = 0;
  11.    while($row=mysql_fetch_array($query)){
  12. $varArray[$i][0]=$row["Player"];
  13. $varArray[$i][1]=$row["Pos"];
  14. $varArray[$i][2]=$row["HR"];
  15. $varArray[$i][3]=$row["RBI"];
  16. }
  17. return $varArray;


This way you have access to the variables through out the entire script and won't have to write your code in wonky places to fit into the HTML [/quote]
  • Carnix
  • Guru
  • Guru
  • User avatar
  • Posts: 1098

Post 3+ Months Ago

Actually...
if he did that, but made the very first array key the value of his sentinel variable... That would solve his issue quickly and easily, without having to write the output from inside the function, and it would allow him to get his stop value. Although, I guess at this point, you could drop that extra variable, and test for the presences of an array, or false in the functions output and not bother with a seperate variable.

.c
  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Posts: 3245
  • Loc: South Africa

Post 3+ Months Ago

Carnix wrote:
Actually...
if he did that, but made the very first array key the value of his sentinel variable... That would solve his issue quickly and easily, without having to write the output from inside the function, and it would allow him to get his stop value. Although, I guess at this point, you could drop that extra variable, and test for the presences of an array, or false in the functions output and not bother with a seperate variable.

.c


Now we are cooking with gas! Loving it!
:twisted:

Post Information

  • Total Posts in this topic: 10 posts
  • Users browsing this forum: No registered users and 89 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
cron
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.