Solved

Cant seem to select a certain field in a MySql DB

Posted on 2010-11-23
7
297 Views
Last Modified: 2012-05-10
Hi folks, I can't seem to be able to select a field from my login table. The table has four fields, an id which is the primary key, the username, the password and the group column which defines the access that each user has. The problem is that I can't seem to be able to make it work! I'm completely frustrated. The select works for the selection of the username and password for comparison, but it doesn't seem to work in the same script for a selection of only the group field...
Here goes the Select script I'm using and I attached the whole script for you guys to work it out...

$sql2="SELECT group FROM $tbl_name WHERE username='$myusername' and password='$mypassword'";
$grupo=mysql_query($sql2);


checklogin.php

<?php
ob_start();
$host="localhost"; // Host name
$username="db_name"; // Mysql username
$password="password"; // Mysql password
$db_name="user_name"; // Database name
$tbl_name="tbLogin"; // Table name


// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// Define $myusername and $mypassword
$myusername=$_POST['myusername'];
$mypassword=$_POST['mypassword'];

// To protect MySQL injection (more detail about MySQL injection)
$myusername = stripslashes($myusername);
$mypassword = stripslashes($mypassword);
$myusername = mysql_real_escape_string($myusername);
$mypassword = mysql_real_escape_string($mypassword);

$sql="SELECT * FROM $tbl_name WHERE username='$myusername' and password='$mypassword'";
$result=mysql_query($sql);
$sql2="SELECT group FROM $tbl_name WHERE username='$myusername' and password='$mypassword'";
$grupo=mysql_query($sql2);

// Mysql_num_row is counting table row
$count=mysql_num_rows($result);
// If result matched $myusername and $mypassword, table row must be 1 row

if($count==1){
// Register $myusername, $mypassword and redirect to file "login_success.php"

session_register("myusername");
session_register("mypassword");
session_register("grupo");

header("location:admin2.php");
}
else {
echo "Wrong Username or Password";
}

ob_end_flush();
?>
----------------------------------------
admin2.php

<?
session_start();
if(session_is_registered(myusername)){
?>
<html xmlns="http://www.w3.org/1999/xhtml"><head>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Admin :: Radio Uerj</title>
<style type="text/css">
.style3 {
	font-family: Arial, Helvetica, sans-serif;
	color: #333333;
}
.style5 {font-size: 12px; color: #333333; font-family: Arial, Helvetica, sans-serif;}
.style6 {
	font-size: 25px;
	font-weight: bold;
}
.style8 {color: #AC232B}
.style11 {font-family: Geneva, Arial, Helvetica, sans-serif; font-size: x-small; }
.formnews {
	margin:0;
	margin-left: 8px;
	border: #999999 solid 1px;
	background: #FFFFFF;
	font-family:Verdana, Geneva, sans-serif;
	font-size: 7pt;
	color: #666666;
	height: 18px;
	width: 200px;	
	float: left;
	text-align:center;
}
.data {
	margin:0;
	margin-left: 8px;
	border: #999999 solid 1px;
	background: #FFFFFF;
	font-family:Verdana, Geneva, sans-serif;
	font-size: 7pt;
	color: #666666;
	height: 18px;
	width: 50px;	
	float: left;
	text-align:center;
}
.formnews2 {
	margin:0;
	margin-left: 8px;
	border: #999999 solid 1px;
	background: #FFFFFF;
	font-family:Verdana, Geneva, sans-serif;
	font-size: 7pt;
	color: #666666;
	width: 200px;	
	float: left;
}
a:link {
	color: #666666;
}
a:visited {
	color: #666666;
}
a:hover {
	color: #666666;
}
a:active {
	color: #666666;
}
.style12 {font-size: x-small}
-->
</style>
</head><body leftmargin="0" topmargin="40" bgcolor="#ffffff">
<table style="border-right: 2px solid rgb(85, 85, 85); border-left: 2px solid rgb(85, 85, 85); border-bottom: 2px solid rgb(85, 85, 85); background-image: url(&quot;imagens/bg_traco.jpg&quot;); background-repeat: repeat;" width="900" align="center" border="0" cellpadding="0" cellspacing="0">
  <tbody><tr>
    <td>
    <table width="100%" border="0" cellpadding="0" cellspacing="0" height="100">
  <tbody><tr>
    <td valign="top" align="center" bgcolor="#333333" height="28">&nbsp;</td>
  </tr>
  <tr>
    <td valign="top" align="center" height="54"><p>&nbsp;</p>      </td>
  </tr>
</tbody></table></td>
  </tr>

  <tr>
  <td>
  <?php  
			if($grupo == 2){
	?>
  <p align="center"><span class="style6"><font color="#333333" face="Arial, Helvetica, sans-serif">ADMINISTRATIVO
</font></span><br>
  <span class="style11"><?php echo $user;?> Logado</span></p>
  <?php
  			}elseif($grupo == 1){
			
			}else{
				?><p align="center"><span class="style6"><font color="#333333" face="Arial, Helvetica, sans-serif">MODULO ADMINISTRATIVO RESTRITO
</font></span><br></p><?php
			}
  ?>
  <table width="700" border="0" align="center">
    <tr>
      <td>
		<?php 
			if($grupo == 2){
		?>
        <form id="form1" name="form1" method="post" action="grava.php">
  			<table width="456" border="0" align="center">
            <tr>
                  <td width="139"><div align="right"><span class="style11">N&uacute;mero do Programa:</span></div></td>
              <td width="307"><div align="left">
                <input name="numero" type="text" class="formnews" id="numero">
              </div></td>
              </tr>
                <tr>
                  <td><div align="right"><span class="style11">Data do Programa:</span></div></td>
                  <td><div align="left">
                    <input name="dia" type="text" class="data" id="dia" value="Dia" size="50" maxlength="3" onFocus="this.value=''">
                    <input name="mes" type="text" class="data" id="mes" value="M&ecirc;s" size="50" maxlength="3" onFocus="this.value=''">
                    <input name="ano" type="text" class="data" id="ano" value="Ano" size="50" maxlength="4" onFocus="this.value=''">
                  </div></td>
                </tr>
                <tr>
                  <td><div align="right"><span class="style11">Programa:</span></div></td>
                  <td><div align="left">
                    <select name="programa" id="programa" class="formnews">
                      <option value="0" selected></option>
                      <option value="1">3&ordm; Grau</option>
                      <option value="2">Agenda Cultural</option>
                      <option value="3">Andante</option>
                      <option value="4">Atitude Cidad&atilde;</option>
                      <option value="5">Cidade Verde</option>
                      <option value="6">CineSom</option>
                      <option value="7">Ensaios</option>
                      <option value="8">EntreLinhas</option>
                      <option value="9">Esporte na Rede</option>
                      <option value="10">Idoso em Foco</option>
                      <option value="11">Isso &eacute; Not&iacute;cia!?</option>
                      <option value="12">Mais Jazz</option>
                      <option value="13">Marketing &amp; Cultura</option>
                      <option value="14">MP3</option>
                      <option value="15">Petisco Digital</option>
                      <option value="16">RadioAtiva</option>
                      <option value="17">Rock.com</option>
                      <option value="18">Rompendo Barreiras</option>
                      <option value="19">Sala de Visita</option>
                      <option value="20">UERJ em Pessoa</option>
                      <option value="21">UERJ Entrevista</option>
                      <option value="22">UERJ Not&iacute;cias</option>
                      <option value="23">Vitrine do Rock</option>
                      <option value="24">Volume 10</option>
                    </select>
                  </div></td>
                </tr>
                <tr>
                  <td><div align="right"><span class="style11">Resumo:</span></div></td>
                  <td><div align="left">
                    <textarea name="resumo" id="resumo" cols="45" rows="5" class="formnews2"></textarea>
                  </div></td>
                </tr>
                <tr>
                  <td><div align="right"><span class="style11">Link:</span></div></td>
                  <td><div align="left">
                    <input type="text" name="link" id="link" class="formnews">
                  </div></td>
                </tr>
                <tr>
                  <td colspan="2"><div align="center">
                    <input type="submit" name="button" id="button" value="   Grava Programa   ">
                  </div></td>
                </tr>
              </table>
        </form>
		<?php 
			}elseif($grupo == 1){
		?>
        <form id="form1" name="form1" method="post" action="grava2.php">
  			<table width="456" border="0" align="center">
            <tr>
                  <td width="139"><div align="right"><span class="style11">ID do Programa:</span></div></td>
              <td width="307"><div align="left">
                <input name="idPrograma" type="text" class="formnews" id="idPrograma">
              </div></td>
              </tr>
                <tr>
                  <td><div align="right"><span class="style11">Aprovado:</span></div></td>
                  <td><div align="left">
                    <select name="aprovado" id="aprovado" class="formnews">
                      <option value="1" selected>Aprovado</option>
                      <option value="0">Reprovado</option>
                    </select>
                  </div></td>
                </tr>
                <tr>
                  <td colspan="2"><div align="center">
                    <input type="submit" name="button" id="button" value="   Registrar Programa   ">
                  </div></td>
                </tr>
              </table>
        </form>
		<?php	
			}else{
				echo "<div align=\"center\"><a href=\"login.php\">Efetue Login</a></div>";							
			}			
		?>
      </td>
    </tr>
  </table>
  <p align="center" class="style12"><a href="javascript:history.go(-1)">voltar</a></p>
  <p>&nbsp;</p>
  <p>&nbsp;</p>
<p align="center"><span class="style3"><span class="style5">E-mail: admin@.com.br </span></span><br>
<span class="style3"><span class="style5"> Tels.: () .- ().</span></span></p>  </td>
  </tr>
  <tr>
    <td><p class="style8">&nbsp;</p>
      <p class="style8"><br>
        </p></td>
  </tr>
  <tr>
    <td></td>
  </tr>
</tbody></table>
</body></html>
<?php
}else{
header("location:login.php");
}
?>

Open in new window

0
Comment
Question by:doRodrigo
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 2

Expert Comment

by:Tiller79188231
Comment Utility
rename your group field to something else... group is a mysql keyword.. so just try group1 or something and see if that works
0
 
LVL 2

Expert Comment

by:benschwartz
Comment Utility
group is a reserved word in mysql. While renaming it might make you saner in the long run, you can also "back tick" it:

SELECT `group` from Table;
0
 

Author Comment

by:doRodrigo
Comment Utility
I just changed the name to group1 in both the database and the script and still doesn't work....

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 2

Expert Comment

by:Tiller79188231
Comment Utility
please add in this line after "$result=mysql_query($sql);"

if (!$result) {die('here is the error: ' . mysql_error());}

then post what error is being thrown.
0
 
LVL 13

Expert Comment

by:dsmile
Comment Utility
I think the query is not the problem.

The problem is that you use mysq_query in a wrong way.

$sql2="SELECT group1 FROM $tbl_name WHERE username='$myusername' and password='$mypassword'";
$grupo=mysql_query($sql2);

after this line, $grupo contains resource, not any data that you can use to compare with 1 or 2 (as coded in admin2.php)

try this instead (note that I already changed group to group1 as you stated earlier
$sql2="SELECT group1 FROM $tbl_name WHERE username='$myusername' and password='$mypassword'";

$result=mysql_query($sql2);

$grupo = mysql_result($result, 0); //this will set selected group value to $grupo.



//now you can use $grupo as you wish

Open in new window

0
 

Author Comment

by:doRodrigo
Comment Utility
dsmile, how to make it not a resource and just the content? Cheers!
0
 
LVL 13

Accepted Solution

by:
dsmile earned 500 total points
Comment Utility
I posted the sample code in the last post (#34198499).
Use that there lines to replace your code online 28 and 29 then your script should be able to work.

how to make it not a resource and just the content?

There're many ways to extract data from a resource (made from mysql_query).

Read about mysql_result and mysql_fetch_xxx here http://php.net/manual/en/book.mysql.php
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now