[Resolvido] Relacionamento de Tabelas e Consultas MySql em PHP

alanrici · Julho 11, 2010

Salve Galera,

É o seguinte, estou precisando desenvolver um site, só que estou meio enferrujado em SQL nem em PHP, o que eu preciso é relacionar minha tabela de clientes com a tabela de pedidos, sabendo que o relacionamento é de 1 para muitos, estou desenvolvendo no Dreamweaver CS3, com phpmyadmin no Wamp5, e também mostrar os dados aos clientes qdo logados, cada cliente verá seus pedidos, eu já fiz a página de login só não estou conseguindo é mostrar nela os dados da tabela pedidos, ou seja, os pedidos dos clientes.

Abaixo estão as estruturas das minhas tabelas

Estrutura da tabela `clientes`

--

CREATE TABLE `clientes` (
`id` int(11) NOT NULL auto_increment,
`senha` varchar(32) NOT NULL,
`nome` varchar(30) NOT NULL,
`empresa` varchar(30) NOT NULL,
`endereco` varchar(30) default NULL,
`bairro` varchar(30) default NULL,
`cidade` varchar(30) default NULL,
`estado` varchar(30) default NULL,
`telefone` int(11) default NULL,
`email` varchar(60) default NULL,
`cep` int(11) default NULL,
`cpfCnpj` int(11) default NULL,
`obs` varchar(255) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

CREATE TABLE `pedidos` (
`id` int(11) NOT NULL auto_increment,
`responsavel` varchar(50) NOT NULL,
`largura` float NOT NULL,
`altura` float NOT NULL,
`quantidade` int(11) NOT NULL,
`resolucao` varchar(50) NOT NULL,
`midia` varchar(50) default NULL,
`id_cliente` int(12) NOT NULL,
PRIMARY KEY (`id`),
KEY `id_cliente` (`id_cliente`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

André Severino · Julho 11, 2010

Poste o código da página onde você quer que mostre os resultados dos pedidos e etc...

Pois assim de cara criar o cód. eh meio complicado, seria mais fácil reaproveitar o seu, então poste ae para nós ver e encontrar uma solução junto com você.

alanrici · Julho 11, 2010

Beleza André,

Taí a página que eu quero mostrar os dados,

pedidos.php

<?php require_once('Connections/ns.php'); ?>
<?php
//initialize the session
if (!isset($_SESSION)) {
session_start();
}

// ** Logout the current user. **
$logoutAction = $_SERVER['PHP_SELF']."?doLogout=true";
if ((isset($_SERVER['QUERY_STRING'])) && ($_SERVER['QUERY_STRING'] != "")){
$logoutAction .="&". htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_GET['doLogout'])) &&($_GET['doLogout']=="true")){
//to fully log out a visitor we need to clear the session varialbles
$_SESSION['MM_Username'] = NULL;
$_SESSION['MM_UserGroup'] = NULL;
$_SESSION['PrevUrl'] = NULL;
unset($_SESSION['MM_Username']);
unset($_SESSION['MM_UserGroup']);
unset($_SESSION['PrevUrl']);

$logoutGoTo = "index.php";
if ($logoutGoTo) {
header("Location: $logoutGoTo");
exit;
}
}
?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break; 
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

$colname_login = "-1";
if (isset($_SESSION['MM_Username'])) {
$colname_login = $_SESSION['MM_Username'];
}
mysql_select_db($database_ns, $ns);
$query_login = sprintf("SELECT * FROM clientes WHERE nome = %s", GetSQLValueString($colname_login, "text"));
$login = mysql_query($query_login, $ns) or die(mysql_error());
$row_login = mysql_fetch_assoc($login);
$totalRows_login = mysql_num_rows($login);

//MX Widgets3 include
require_once('includes/wdg/WDG.php');
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:wdg="http://ns.adobe.com/addt">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Comunicação Visual NS - 1ª Impressão</title>
<link href="estilo.css" rel="stylesheet" type="text/css" />
<script src="Scripts/AC_RunActiveContent.js" type="text/javascript"></script>
<script src="SpryAssets/SpryMenuBar.js" type="text/javascript"></script>

<style type="text/css">
<!--
.style2 {
font-family: Verdana;
font-size: 9px;
}
.style3 {
font-size: 12px
}
.style9 {
font-size: 16px;
color: #000000;
}
.style12 {
font-family: Arial, Helvetica, sans-serif;
font-size: 14;
font-weight: normal;
}
.style13 {font-size: 16px}
.style16 {font-size: 14px; font-family: Arial, Helvetica, sans-serif;}
.style19 {
color: #003366;
font-size: 18px;
}
.style20 {color: #0066CC}
.style21 {color: #0066FF}
.style25 {color: #FFFFFF; font-size: 16; }
-->
</style>
<link href="SpryAssets/SpryMenuBarHorizontal.css" rel="stylesheet" type="text/css" />
<script type="text/javascript" src="includes/common/js/sigslot_core.js"></script>
<script src="includes/common/js/base.js" type="text/javascript"></script>
<script src="includes/common/js/utility.js" type="text/javascript"></script>
<script type="text/javascript" src="includes/wdg/classes/MXWidgets.js"></script>
<script type="text/javascript" src="includes/wdg/classes/MXWidgets.js.php"></script>
<script type="text/javascript" src="includes/wdg/classes/Calendar.js"></script>
<script type="text/javascript" src="includes/wdg/classes/SmartDate.js"></script>
<script type="text/javascript" src="includes/wdg/calendar/calendar_stripped.js"></script>
<script type="text/javascript" src="includes/wdg/calendar/calendar-setup_stripped.js"></script>
<script src="includes/resources/calendar.js"></script>
<link href="includes/skins/mxkollection3.css" rel="stylesheet" type="text/css" media="all" />
</head>

<body>
<div id="main">
<div id="cabecalho" >
<script type="text/javascript">
AC_FL_RunContent( 'codebase','http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=9,0,28,0','width','760','height','201','src','imagens/cabecalho','quality','high','pluginspage','http://www.adobe.com/shockwave/download/download.cgi?P1_Prod_Version=ShockwaveFlash','movie','imagens/cabecalho' ); //end AC code
</script>
<noscript>
<object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=9,0,28,0" width="760" height="201">
<param name="movie" value="imagens/cabecalho.swf" />
<param name="quality" value="high" />
<embed src="imagens/cabecalho.swf" quality="high" pluginspage="http://www.adobe.com/shockwave/download/download.cgi?P1_Prod_Version=ShockwaveFlash" type="application/x-shockwave-flash" width="760" height="201"></embed>
</object>
</noscript>
<table width="774" height="80">
<tr>

<th width="118" height="74" align="left" valign="top" scope="col"> </th>
<th width="512" align="center" valign="top" scope="col"><ul id="MenuBar1" class="MenuBarHorizontal">
<li><a href="index.php">Home</a> </li>
<li><a href="empresa.php">Empresa</a></li>
<li><a href="estrutura.php">Estrutura</a> </li>
<li><a href="contato.php">Contato</a></li>
</ul> </th>
<th width="128" valign="top" scope="col"> </th>
</tr>
</table>
</div>
<div id="corpopedido" >
<table width="771" height="446" border="1">
<tr>
<td width="120" height="440"> </td>
<td width="509" valign="top"><p>Bem Vindo <?php echo $row_login['nome']; ?><br />
Empresa:<?php echo $row_login['empresa']; ?></p>
<p>Código cliente:<?php echo $row_login['id']; ?></p>
<p>Responsável: <br />
</p>
<p>Medidas: X </p>
<p>Quantidade: peças</p>
<p>Tamanho: </p>
<p><a href="<?php echo $logoutAction ?>">Sair</a><br />
</p></td>
<td width="120"> </td>
</tr>
</table>


</div>
<div id="rodape" >
<table width="762" height="55" border="0">
<tr>
<td width="348" height="51"> </td>
<td width="50"> </td>
<td width="350"> </td>
</tr>
</table>
</div>
</div>

<script type="text/javascript">
<!--
var MenuBar1 = new Spry.Widget.MenuBar("MenuBar1", {imgDown:"SpryAssets/SpryMenuBarDownHover.gif", imgRight:"SpryAssets/SpryMenuBarRightHover.gif"});
//-->
</script>
</body>
</html>
<?php
mysql_free_result($login);
?>

alanrici · Julho 14, 2010

Galera,

essa parte eu consegui resolver, ficou assim, obrigado à quem leu.

<?php require_once('Connections/ns.php'); ?>
<?php
//initialize the session
if (!isset($_SESSION)) {
  session_start();
}

// ** Logout the current user. **
$logoutAction = $_SERVER['PHP_SELF']."?doLogout=true";
if ((isset($_SERVER['QUERY_STRING'])) && ($_SERVER['QUERY_STRING'] != "")){
  $logoutAction .="&". htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_GET['doLogout'])) &&($_GET['doLogout']=="true")){
  //to fully log out a visitor we need to clear the session varialbles
  $_SESSION['MM_Username'] = NULL;
  $_SESSION['MM_UserGroup'] = NULL;
  $_SESSION['PrevUrl'] = NULL;
  unset($_SESSION['MM_Username']);
  unset($_SESSION['MM_UserGroup']);
  unset($_SESSION['PrevUrl']);
	
  $logoutGoTo = "index.php";
  if ($logoutGoTo) {
    header("Location: $logoutGoTo");
    exit;
  }
}
?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$colname_login = "-1";
if (isset($_SESSION['MM_Username'])) {
  $colname_login = $_SESSION['MM_Username'];
}
mysql_select_db($database_ns, $ns);
$query_login = sprintf("SELECT * FROM clientes WHERE nome = %s", GetSQLValueString($colname_login, "text"));
$login = mysql_query($query_login, $ns) or die(mysql_error());
$row_login = mysql_fetch_assoc($login);
$totalRows_login = mysql_num_rows($login);

$maxRows_rspedido = 10;
$pageNum_rspedido = 0;
if (isset($_GET['pageNum_rspedido'])) {
  $pageNum_rspedido = $_GET['pageNum_rspedido'];
}
$startRow_rspedido = $pageNum_rspedido * $maxRows_rspedido;

$colname_rspedido = "-1";
if (isset($_GET['id'])) {
  $colname_rspedido = $_GET['id'];
}
mysql_select_db($database_ns, $ns);
$query_rspedido = sprintf("SELECT * FROM pedidos WHERE id_cliente = %s ORDER BY id DESC", GetSQLValueString($row_login['id'], "int"));
$query_limit_rspedido = sprintf("%s LIMIT %d, %d", $query_rspedido, $startRow_rspedido, $maxRows_rspedido);
$rspedido = mysql_query($query_limit_rspedido, $ns) or die(mysql_error());
$row_rspedido = mysql_fetch_assoc($rspedido);

if (isset($_GET['totalRows_rspedido'])) {
  $totalRows_rspedido = $_GET['totalRows_rspedido'];
} else {
  $all_rspedido = mysql_query($query_rspedido);
  $totalRows_rspedido = mysql_num_rows($all_rspedido);
}
$totalPages_rspedido = ceil($totalRows_rspedido/$maxRows_rspedido)-1;

//MX Widgets3 include
require_once('includes/wdg/WDG.php');
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:wdg="http://ns.adobe.com/addt">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Comunicação Visual NS - 1ª Impressão</title>
<link href="estilo.css" rel="stylesheet" type="text/css" />
<script src="Scripts/AC_RunActiveContent.js" type="text/javascript"></script>
<script src="SpryAssets/SpryMenuBar.js" type="text/javascript"></script>
<link href="SpryAssets/SpryMenuBarHorizontal.css" rel="stylesheet" type="text/css" />
<script type="text/javascript" src="includes/common/js/sigslot_core.js"></script>
<script src="includes/common/js/base.js" type="text/javascript"></script>
<script src="includes/common/js/utility.js" type="text/javascript"></script>
<script type="text/javascript" src="includes/wdg/classes/MXWidgets.js"></script>
<script type="text/javascript" src="includes/wdg/classes/MXWidgets.js.php"></script>
<script type="text/javascript" src="includes/wdg/classes/Calendar.js"></script>
<script type="text/javascript" src="includes/wdg/classes/SmartDate.js"></script>
<script type="text/javascript" src="includes/wdg/calendar/calendar_stripped.js"></script>
<script type="text/javascript" src="includes/wdg/calendar/calendar-setup_stripped.js"></script>
<script src="includes/resources/calendar.js"></script>
<link href="includes/skins/mxkollection3.css" rel="stylesheet" type="text/css" media="all" />
<style type="text/css">
<!--
.style1 {
	font-size: 14px;
	font-weight: bold;
	color: #666666;
}
.style3 {
	font-size: 14;
	font-weight: bold;
}
-->
</style>
</head>

<body>
<div id="main">
  <div id="cabecalho" >
    <script type="text/javascript">
AC_FL_RunContent( 'codebase','http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=9,0,28,0','width','760','height','201','src','imagens/cabecalho','quality','high','pluginspage','http://www.adobe.com/shockwave/download/download.cgi?P1_Prod_Version=ShockwaveFlash','movie','imagens/cabecalho' ); //end AC code
    </script>
    <noscript>
    <object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=9,0,28,0" width="760" height="201">
      <param name="movie" value="imagens/cabecalho.swf" />
      <param name="quality" value="high" />
      <embed src="imagens/cabecalho.swf" quality="high" pluginspage="http://www.adobe.com/shockwave/download/download.cgi?P1_Prod_Version=ShockwaveFlash" type="application/x-shockwave-flash" width="760" height="201"></embed>
    </object>
    </noscript>
    <table width="774" height="80">
      <tr>
        
        <th width="118" height="74" align="left" valign="top" scope="col"> </th>
        <th width="512" align="center" valign="top" scope="col"><ul id="MenuBar1" class="MenuBarHorizontal">
              <li><a href="index.php">Home</a>                  </li>
            <li><a href="empresa.php">Empresa</a></li>
            <li><a href="estrutura.php">Estrutura</a>              </li>
            <li><a href="contato.php">Contato</a></li>
        </ul>        </th>
        <th width="128" valign="top" scope="col"> </th>
      </tr>
    </table>
  </div>
  <div id="corpopedido" >
  <table width="771" height="446">
  <tr>
    <td width="120" height="440"> </td>
    <td width="509" valign="top"><p> </p>
      <table width="715">
        <tr>
          <td width="180" height="39"><span class="style3">Cliente: <?php echo $row_login['nome']; ?><br />
Empresa: <?php echo $row_login['empresa']; ?></span></td>
          <td width="154" align="center"><a href="novo_pedido.php" class="style1">Novo Pedido</a></td>
          <td width="86"> </td>
          <td width="229"> </td>
          <td width="32"><a href="<?php echo $logoutAction ?>"><strong>Sair</strong></a></td>
        </tr>
      </table>
      <p> </p>
      <table width="735" height="47" border="1">
        <tr align="center">
          <td width="108">Nº Pedido</td>
          <td width="137">Responsavel</td>
          <td width="108">Largura</td>
          <td width="100">Altura</td>
          <td width="130">Quantidade</td>
          <td width="122">Resolucao</td>
          </tr>
        <?php do { ?>
        <tr align="center">
          <td><?php echo $row_rspedido['id']; ?></td>
          <td><?php echo $row_rspedido['responsavel']; ?></td>
          <td><?php echo $row_rspedido['largura']; ?></td>
          <td><?php echo $row_rspedido['altura']; ?></td>
          <td><?php echo $row_rspedido['quantidade']; ?></td>
          <td><?php echo $row_rspedido['resolucao']; ?></td>
          </tr>
        <?php } while ($row_rspedido = mysql_fetch_assoc($rspedido)); ?>
      </table>
      <p> </p>
      <p><a href="<?php echo $logoutAction ?>"></a><br />
        </p></td>
    <td width="120"> </td>
  </tr>
</table>

    
  </div>
    <div id="rodape" >
      <table width="762" height="55" border="0">
        <tr>
          <td width="345" height="51"> </td>
          <td width="50"> </td>
          <td width="345"> </td>
        </tr>
      </table>
</div>
</div>

<script type="text/javascript">
<!--
var MenuBar1 = new Spry.Widget.MenuBar("MenuBar1", {imgDown:"SpryAssets/SpryMenuBarDownHover.gif", imgRight:"SpryAssets/SpryMenuBarRightHover.gif"});
//-->
</script>
</body>
</html>
<?php
mysql_free_result($login);

mysql_free_result($rspedido);
?>

Entrar

Arquivado

[Resolvido] Relacionamento de Tabelas e Consultas MySql em PHP

Recommended Posts

alanrici 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

André Severino 3

Compartilhar este post

Link para o post

Compartilhar em outros sites

alanrici 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

alanrici 0

Compartilhar este post

Link para o post

Compartilhar em outros sites

Fixar div até atingir uma certa altura

Ajuda com Extends e envio para o banco

PHP+Codeginiter - Orientação para Impressão

Este projeto é apoiado pelas empresas

Fóruns

Tempo Real

Informação importante