Solved

Cant seem to select a certain field in a MySql DB

Posted on 2010-11-23
7
298 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
ID: 34196756
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
ID: 34196877
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
ID: 34197017
I just changed the name to group1 in both the database and the script and still doesn't work....

0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 2

Expert Comment

by:Tiller79188231
ID: 34197154
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
ID: 34198499
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
ID: 34198779
dsmile, how to make it not a resource and just the content? Cheers!
0
 
LVL 13

Accepted Solution

by:
dsmile earned 500 total points
ID: 34199494
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
000webhost.com default error log 1 28
Curl & PHP Command Help 4 23
comma true 6 33
sql server insert 12 30
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 …
Creating and Managing Databases with phpMyAdmin in cPanel.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

777 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