Cant combined two mysql tables?

[kendallkamikaze]

Alright so the coding I have on one page allows you to submit requests into 'associations' on our game. My issue is that it's showing all 'horses', including ones that were already registered with that specific 'association', instead of showing just horses that have not been registered with that 'association'. I would have been able to code this myself [ i know VERY LITTLE php yet own a game that coded in php, go figure? ] if the associations were listed under the mysql table horsedata only. Instead the associations are listed under 'Registrations'.

 

So basically I have these two tables on mysql; horsedata & Registrations -- The code below is what shows all the horses..but I just want the horses that have not been registered with that association previously to show up. Unfortunately for me, it's on a table other than horsedata...

 

I guess bottom line is...how do i get it to check and see if the horse hasn’t been registered with the association which is on another mysql table? Sorry to make this so confusing, I was trying to make more sense of it O.O

 

Code which gets all horses

<?php

$hid=$_GET['id'];

$sql="SELECT * FROM horsedata WHERE Owner='$id'";
$result=mysql_query($sql);

while($r=mysql_fetch_array($result))
{	

  
$horseid=$r["id"];
   $name=$r["Name"];
$assoid=$_GET['id'];
   

  
   echo "<center> <form action='association_apply.php' method='post'> <input type='hidden' value='$horseid' name='horseid'> <input type='hidden' value='$assoid' name='assoid'> <input type='submit' value='$name'></form> </center> ";

}?>

 

Mysql Registration table info:  id [for when a horse was registered], assoname [name of the association], assoid [association id number], horseid [id of the horse]

[redarrow]

add i think  AND assoname='the name of assoname'

[kendallkamikaze]

I dont think that will work because assoname is on a different table than horsedata table,w hich is where its pulling the info from.

[redarrow]

but is that the result you want all you do is a join.

 

[kendallkamikaze]

the result i want is for the horses who are already in the association to not show up on the list to register.

 

as of right now they all show up, i just want the ones that havent been registered in a specific assoication to showu p.

[kendallkamikaze]

maybe its supose to be an if statement like

 

if ($assoname = '$name') then only display the horses that dont have that associations name? i dont know what the codes supose to look like haah

[sasa]

$sql="SELECT * FROM horsedata 
LEFT JOIN Registration ON Registration.horseid = horsedata.horseid
WHERE Owner='$id' AND Registration.assoname != 'specific association'";

[kendallkamikaze]

$sql="SELECT * FROM horsedata 
LEFT JOIN Registration ON Registration.horseid = horsedata.horseid
WHERE Owner='$id' AND Registration.assoname != 'specific association'";

 

<?php

$assoname=$_GET['assoname']; 

$sql="SELECT * FROM horsedata 
LEFT JOIN Registration ON Registration.horseid = horsedata.horseid
WHERE Owner='$id' AND Registration.assoname != '$assoname'";

while($r=mysql_fetch_array($result))
{	

  
$horseid=$r["id"];
   $name=$r["Name"];
$assoid=$_GET['id'];
   

  
   echo "<center> <form action='association_apply.php' method='post'> <input type='hidden' value='$horseid' name='horseid'> <input type='hidden' value='$assoid' name='assoid'> <input type='submit' value='$name'></form> </center> ";

}?>

 

Now this code its semi-functional seeing is there is no errors, but the horses that are not registered in the association are not showing up.

[redarrow]

did you lose a

 

$res=mysql_query($sql)or die(mysql_error());

 

 

[kendallkamikaze]

did you lose a

 

$resmysql_query($sql)or die(mysql_error());

 

nope

[redarrow]

your playing games.lol

<?php

$assoname=$_GET['assoname']; 

$sql="SELECT * FROM horsedata 
LEFT JOIN Registration ON Registration.horseid = horsedata.horseid
WHERE Owner='$id' AND Registration.assoname != '$assoname'";

$result=mysql_query($sql)or die(mysql_error());

while($r=mysql_fetch_array($result))
{   

  
$horseid=$r["id"];
   $name=$r["Name"];
$assoid=$_GET['id'];
   

  
   echo "<center> <form action='association_apply.php' method='post'> <input type='hidden' value='$horseid' name='horseid'> <input type='hidden' value='$assoid' name='assoid'> <input type='submit' value='$name'></form> </center> ";

}?>

[kendallkamikaze]

that made the code error up:

 

"Table 'collegh9_onestride.Registration' doesn't exist"

 

when there is a mysql table named Registration

[redarrow]

better try this then i say.

<?php

$assoname=$_GET['assoname']; 

$sql="SELECT * FROM horsedata,Registration
LEFT JOIN Registration ON Registration.horseid = horsedata.horseid
WHERE Owner='$id' AND Registration.assoname != '$assoname'";

$result=mysql_query($sql)or die(mysql_error());

while($r=mysql_fetch_array($result))
{   

  
$horseid=$r["id"];
   $name=$r["Name"];
$assoid=$_GET['id'];
   

  
   echo "<center> <form action='association_apply.php' method='post'> <input type='hidden' value='$horseid' name='horseid'> <input type='hidden' value='$assoid' name='assoid'> <input type='submit' value='$name'></form> </center> ";

}?>

[kendallkamikaze]

ahha! I found the error

 

typo on my part but now the ids are just repeating themselves over and over and over

 

<?php

$assoname=$_GET['assoname']; 

$sql="SELECT * FROM horsedata 
LEFT JOIN registration ON registration.horseid = horsedata.id
WHERE Owner='$id' AND registration.assoname != '$assoname'";

$result=mysql_query($sql)or die(mysql_error());

while($r=mysql_fetch_array($result))
{   	

  
$horseid=$r["id"];
   $name=$r["Name"];
$assoid=$_GET['id'];
   

  
   echo "<center> <form action='association_apply.php' method='post'> <input type='hidden' value='$horseid' name='horseid'> <input type='hidden' value='$assoid' name='assoid'> <input type='submit' value='$name'></form> </center> ";

} ?> 

[redarrow]

what this do please a guess i take it lol.

probably wrong syntax....

 

 

<?php

$sql="SELECT  registration.horseid,registration.assoname
             ,horsedata.id,horsedata.Owner
              FROM horsedata,registration
              WHERE 
              registration.horseid = horsedata.id
              AND
              horsedata.Owner='$id' 
             AND 
             registration.assoname != '$assoname'";

?>

[sasa]

oops

add GROUP BY horsedata.id to the end of query

[kendallkamikaze]

oops

add GROUP BY horsedata.id to the end of query

 

well now its not repeating but its still showing all horses, ones that are already apart of the association, and the ones that arnt apart of the association, but i just need to be able to see the ones that are not in the association

[kendallkamikaze]

wait, i lied, its only showing horses that ARE registered, I need it to be the opposite where you see only horses that are not registered and the ones that are, you cant see.

 

 

Edit:

 

this is odd...theres like 3 horses that are not registered to the association im looking at, that show up, the rest are already registered, but then theres like 10 horses that are not registered that dont show up at all.

[sasa]

$sql="SELECT * FROM horsedata 
LEFT JOIN registration ON registration.horseid = horsedata.id
WHERE Owner='$id' AND (registration.assoname != '$assoname' OR ISNULL(registration.assoname)) 
GROUP BY horsedata.id";

[kendallkamikaze]

$sql="SELECT * FROM horsedata 
LEFT JOIN registration ON registration.horseid = horsedata.id
WHERE Owner='$id' AND (registration.assoname != '$assoname' OR ISNULL(registration.assoname)) 
GROUP BY horsedata.id";

 

now all are showing up...so i just need a code that will make it so the hroses that are registered already dont show up

[sasa]

$sql="SELECT * FROM horsedata 
LEFT JOIN registration ON registration.horseid = horsedata.id
WHERE Owner='$id' AND ISNULL(registration.assoname) 
GROUP BY horsedata.id";

[kendallkamikaze]

now its working but it errors the code following it now, because its not inserting the horseid:

 

<?php

include 'header.php';

$sql="INSERT INTO assorequest (horseid, assoid, ownerid)
VALUES
('$_POST[horseid]','$_POST[assoid]','$id')";
if (!mysql_query($sql))
  {
  die('You have already submitted a request to this association from this horse'); include 'footer.php'; exit;}

echo "<BR><BR><font size='5pt' face='comic sans ms'><B>Registration Request Submitted</b></font><BR><BR>";

?>

<?php

include 'footer.php';

?>

[redarrow]

my bash.

<?php

include 'header.php';

$sql="INSERT INTO assorequest (horseid, assoid, ownerid)
VALUES
('{$_POST['horseid']}','{$_POST['assoid']}','$id')";
if (!mysql_query($sql))
  {
  die('You have already submitted a request to this association from this horse'); include 'footer.php'; exit;}

echo "<BR><BR><font size='5pt' face='comic sans ms'><B>Registration Request Submitted</b></font><BR><BR>";

?>

<?php

include 'footer.php';

?>

[sasa]

try[if (!mysql_query($sql))
  {
echo $sql,'<br />';
echo mysql_error(),'<br />';
  die('You have already submitted a request to this association from this horse');}

and post the error

 

 

[kendallkamikaze]

my bash.

<?php

include 'header.php';

$sql="INSERT INTO assorequest (horseid, assoid, ownerid)
VALUES
('{$_POST['horseid']}','{$_POST['assoid']}','$id')";
if (!mysql_query($sql))
  {
  die('You have already submitted a request to this association from this horse'); include 'footer.php'; exit;}

echo "<BR><BR><font size='5pt' face='comic sans ms'><B>Registration Request Submitted</b></font><BR><BR>";

?>

<?php

include 'footer.php';

?>

 

still inserts a blank horse id