How to create query in log in that compares the id using php ?

[aixen083]

So this is the scenario, i have 3 tables in my database voters.tbl, candidates.tbl, organisation.tbl

so if the voters registered by the organisation and the candidates registered in the same organisation like the voters.

The user/Voters must see only the candidates that has the same id of a voter.

Let's say

 

ORGANISATION.TBL                                           

----------------------                                CANDIDATES.TBL

org_id:  1                                             -------------------

org_name: umbrella                           candidates_id: 1

                                                            firstname: ramon

                                                            lastname: mclights

                                                            org_id: 1

 

VOTERS.TBL

------------------

voters_id: 1

firsname: Anne

lastname: Sunga

org_id: 1

 

 

The bold text are the value of the column 

Please help me here how can i Query and compares the id of candidates and voters

[ginerjm]

More info please.

[aixen083]

More info please.

Sir help me.. how i can query that if the voters org_id is equal to the candidates org_id

then if I am Logging in to the system the voters only see the candidates equal to the voters org_id. 

[Barand]

something like this

SELECT c.firstname
    , c.lastname
FROM candidates c
    INNER JOIN voters v USING (org_id)
WHERE v.voters_id = $loggedInID

Edited February 16, 2015 by Barand

[aixen083]

 

something like this

SELECT c.firstname
    , c.lastname
FROM candidates c
    INNER JOIN voters v USING (org_id)
WHERE v.voters_id = $loggedInID

the USING (org_id) does not work very well

[Barand]

Change it to

ON c.org_id = v.org_id

[aixen083]

 

Change it to

ON c.org_id = v.org_id

excuse me sir what is the importance of this script/code  ? 

$loggedInID

[Barand]

The id of the logged-in voter who wants to see the candidates in his/her organization. I have no idea how you are storing it and what your variable is but I thought that was a self-explanatory name for example purposes.

[aixen083]

The id of the logged-in voter who wants to see the candidates in his/her organization. I have no idea how you are storing it and what your variable is but I thought that was a self-explanatory name for example purposes.

 

Sir this is my 1st query i made

$login_query=mysql_query("SELECT voters.voters_id, candidates.candidates_id, candidates.firstname, candidates.lastname, candidates.img FROM candidates INNER JOIN voters On voters.org_id = candidates.org_id WHERE id_number='$id_number' and password='$password' and status='Unvoted'")or die(mysql_error());

so i try to create new query in you suggestions.

$login_query=mysql_query("SELECT v.voters_id, c.firstname, c.lastname, c.image FROM candidates c INNER JOIN voters v ON v.org_id = c.org_id WHERE id_number='$id_number' and password='$password' and v.voters_id = $id_number and status='Unvoted'")or die(mysql_error());

[Barand]

Which of your tables contains "id_number" column. You didn't mention that before (I assume that "password" and "status" are in the voters table)

[aixen083]

Which of your tables contains "id_number" column. You didn't mention that before (I assume that "password" and "status" are in the voters table)

the id_number is the username of the voters.

[Barand]

 

 

WHERE id_number='$id_number' and password='$password' and v.voters_id = $id_number

 

Which of those two fields should match the value in $id_number?

 

(If the answer is both, why are you storing it twice?)

[aixen083]

the id_number, password, status are in the same column of voters table.

[Barand]

 

 

WHERE id_number='$id_number' and password='$password' and v.voters_id = $id_number

 

Which of those two fields should match the value in $id_number?

 

(If the answer is both, why are you storing it twice?)

[Barand]

the id_number, password, status are in the same column of voters table.

 

What? Please tell tell that's a typing error.

[aixen083]

Which of those two fields should match the value in $id_number?

 

(If the answer is both, why are you storing it twice?)

 

my bad 1st i only store 1 id number for voters.. the problem is that how the voters determine if that candidates are same org_id as the voters.org_id because 

[Barand]

 the problem is that how the voters determine if that candidates are same org_id as the voters.org_id because 

 

That's what the JOIN on the org_id is for.

 

 

 

my bad 1st i only store 1 id number for voters

Then you would only use that column in the query

SELECT v.id_number
    , c.firstname
    , c.lastname
    , c.image 
FROM candidates c 
    INNER JOIN voters v ON v.org_id = c.org_id 
WHERE id_number='$id_number' AND password='$password' AND status='Unvoted'

Edited February 19, 2015 by Barand

[aixen083]

 

That's what the JOIN on the org_id is for.

 

 

 

Then you would only use that column in the query

SELECT v.id_number
    , c.firstname
    , c.lastname
    , c.image 
FROM candidates c 
    INNER JOIN voters v ON v.org_id = c.org_id 
WHERE id_number='$id_number' AND password='$password' AND status='Unvoted'

 

it works fine but it didn't get the exact i want to do.. any ways. do you have another option on how to perform this ?

[Barand]

Not without seeing your data and how it is really set up

[aixen083]

this is my login page..

<?php include('header.php');
session_start();
?>
  <body data-spy="scroll" data-target=".bs-docs-sidebar">
    <!-- Navbar
    ================================================== -->
<?php include('navbar.php');?>

<!-- Subhead
================================================== -->
  <div class="container">
  
    <!-- Docs nav
    ================================================== -->
    <div class="row">
	    <div class="span6 offset3">
		
        <!-- Dropdowns
        ================================================== -->
		
        <section id="dropdowns">
		<div class="login">
			
		
		
				  <div class="bs-docs-example">
		<div class="alert alert-info"><strong><i class="icon-user icon-large"></i> Voters Login</strong></div>
		<hr>
		<h5>Please Enter the Details Required Below</h5>
       <hr>
	
     <form class="form-horizontal" method="POST">
	 
    <div class="control-group">
    <label class="control-label" for="inputEmail">ID Number:</label>
    <div class="controls">
    <input type="text" name="id_number" id="inputEmail" placeholder="ID Number" required>
    </div>
    </div>
	
	
    <div class="control-group">
    <label class="control-label" for="inputPassword">Password:</label>
    <div class="controls">
    <input type="password" name="password" id="inputPassword" placeholder="Password" required>
    </div>
    </div>
    <div class="control-group">
    <div class="controls">
	<script type="text/javascript">
    jQuery(document).ready(function() {
        $('#save').tooltip('show');
        $('#save').tooltip('hide');
    })
</script>
    <button data-placement="right" id="save"  title="Click to Login" type="submit" name="login" class="btn btn-success"><i class="icon-signin icon-large"></i> Sign in</button>
           <a href="register/index.php" >   Register </a> 
 
	
    </div>
	<?php
	if (isset($_POST['login'])){
	$id_number=$_POST['id_number'];
	$password=md5($_POST['password']);

	 $login_query=mysql_query("SELECT voters.id_number, voters.voters_id, candidates.candidates_id, candidates.firstname, candidates.lastname, candidates.img FROM candidates INNER JOIN voters ON voters.org_id = candidates.org_id WHERE id_number='$id_number' AND password='$password' AND status='Unvoted'")or die(mysql_error());

	$row=mysql_fetch_array($login_query);
	$login_query1=mysql_query("select * from voters where id_number='$id_number' and password='$password' and status='Voted'")or die(mysql_error());
	$count1=mysql_num_rows($login_query1);
	$count=mysql_num_rows($login_query);
	$id=$row['voters_id'];
	if ($count > 0){
	$_SESSION['id']=$id;

	
	?>
	<script>
	

	window.location="vote_home.php";
	</script>
	<?php
	}else if($count1 == 1){ ?>
		<br>
<div class="alert alert-danger">You can only vote once per Organization.</div>	
	<?php }else{ ?>
	<br>
<div class="alert alert-danger"><strong>Access Denied!</strong>    Please Check your Organization.</div>	
<?php	
	}
	}
	?>
	</div>
    </div>
    </form>
        </section>  	
      </div>
    </div>
  </div>
  </div>


<?php include('footer.php'); ?>
<?php include('script.php'); ?>
  </body>
</html>

 and after that this is my vote_home.php

<?php include('header.php');?>

<?php include('session.php');?>


<body data-spy="scroll" data-target=".bs-docs-sidebar"><?php include('navbar_home.php'); ?>
<div class="container"><div class="row"><div class="span4">

<section id="dropdowns"><?php include('president.php');?></section>
<section id="dropdowns"><?php include('secretary.php');?></section>  
<section id="dropdowns"><?php include('treasurer.php'); ?></section>
</div>

<div class="span3"><?php include('balot.php'); ?></div>	
	
<div id="jk" class="span4 pull-right">
<section id="dropdowns"><?php include('vice_president.php'); ?></section>  
<section id="dropdowns"><?php include('auditor.php'); ?></section>
<section id="dropdowns"><?php include('pro.php'); ?></section>
</div></div></div>
<?php include('footer.php'); ?><?php include('script.php'); ?>
</body>
</html>

now this is my president.php script

<div class="span5">

<?php
$query_vote=mysql_query("select * from votes where position='President' and voters_id='$session_id'");
$count=mysql_num_rows($query_vote);
$row=mysql_fetch_array($query_vote);
$id=$row['vote_id'];

if ($count==1){ ?>
<script type="text/javascript">
jQuery(document).ready(function() {
$('#delete<?php echo $id; ?>').tooltip('show');
$('#delete<?php echo $id; ?>').tooltip('hide');
})
</script>
<h6>My Candidate for President</h6>
<div class="none"><img class="img-polaroid" src="admin/<?php echo $row['img']; ?>" width="88" height="88"/>
<a><?php echo $row['firstname']." ".$row['lastname']; ?></a>
<a class="btn btn-danger" data-placement="right" id="delete<?php echo $id; ?>"  title="Click to Remove"  href="#<?php echo $id; ?>"  data-toggle="modal">  <i class="icon-remove icon-large">
</i></a>
<!---delete modal -->
 <?php include('delete_user_modal.php'); ?>
<!---delete modal -->
<?php
}else{
?>	
<h6>Candidate for President</h6>
<?php

	$result = mysql_query("SELECT voters.id_number, candidates.candidates_id, candidates.firstname, candidates.lastname, candidates.img FROM candidates INNER JOIN voters ON voters.org_id = candidates.org_id WHERE position='President'");



while($row=mysql_fetch_assoc($result))
{

$id=$row['candidates_id'];
 ?>	

			<script type="text/javascript">
    jQuery(document).ready(function() 
	{
        $('#a<?php echo $id; ?>').tooltip('show');
        $('#a<?php echo $id; ?>').tooltip('hide');
    })
</script>			
<div class="picture"><img data-placement="bottom" id="a<?php echo $id; ?>"  title="Drag the Image to the ballot Box to Vote for 
<?php echo $row['firstname']." ".$row['lastname']; ?>" class="img-polaroid" src="admin/<?php echo $row['img']; ?>" width="88" height="128"/>
<br>
    
<a><?php echo $row['firstname']." ".$row['lastname']; ?></a>
</div>
<?php 				
}
?>


<?php } ?>
	
		<div>

[Barand]

If you don't know code from data then you are beyond help. Bye.

[aixen083]

If you don't know code from data then you are beyond help. Bye.

I only needs idea. ANY WAYS Thanks for the help

[Barand]

I gave you the idea - you do a JOIN on org_id to get records with matching values in that column