How to do operations with table fields in mysql?

Question

How to do operations with table fields in mysql?

Navigation

#1 by (2 votes)

0

I have a form that sends data to a table in mysql called (regis) and it has the fields (Name, Address, Phone, Job_ID, Amount, Date_input, Advance), in my field (Job_id) goes the value of my option selected, since I put a field with several options and I put them (1,2,3, etc) depending on which one they choose

<div class="col-md-5">
      <select class="form-control"  name="txt2">
       
        <option selected value="0"> Elige una opcion </option>
        <optgroup label="Porcelana">
        <option value="1">Corona metal porcelana</option>
        <option value="2">Corona veneer</option>
        <option value="3">Corona meriland</option>
        <option value="4">Montado de porcelana</option>
        </optgroup>

I have another table called (works) with the fields (id, code, name, cost) that I show on my page, which works as a price list, the user can modify the costs, remove or add a new product

Now what I want to do, is that when searching for a specific client by name, I will see the following information displayed,

(Name, Address, telephone, date of entry, Name of work, Cost, Amount, Total, Advance, Subtraction),

in my "Job Name" field, I want the name to appear depending on the value of the field (Job_id), for example, in the table that first shows them the value in the Job_id field is 1, which corresponds to the name "crown metal pòrcelana", from the table "works", because it has the id = 1, do not know if it is understood?

and then to fill the Cost and Total field, I need to do the operation depending on the Work_ID of table 1, for example in my table I have 1, which corresponds to id = 1 of the table "jobs" which has the cost of $ 500, then for the Total it would be 500 for 2, which is the value of the "Quantity" field of table 1

How can I do those operations and show them in the table? or what methods can I use?

I leave my code that I have, it is a select from my register table, but I have missing fields to fill out, I want all the information that I mentioned to them to come out and to save it, so I can modify it later, but I do not know as

 
	<? include("config.php");
	//Instaciamos la clase de base de datos
 	$db = new Clasedb();
 	//Llamamos a la funcion para conectar a la base de datos
 	$db->MySQL();
 	//Consultamos la tabla regis
 	$consulta = $db->consulta("SELECT * FROM regis where Nombre like '%$buscar%'");
	?>
<form class="form-horizontal" name="form1" method="post" action="consultar.php">
	<br>
	<br>
	<br>
	<br>
	<br>
	
	<fieldset class="scheduler-border">
		<legend class="scheduler-border">CONSULTA DE REGISTROS</legend>
			<div class="form-group">
				<div class="col-md-5">
					<input  class="form-control" type="text" name="buscar" placeholder="nombre del cliente">
				</div>
			</div>
			<button class="btn btn-primary">BUSCAR</button>
	</fieldset>
	
	
	
	<?php
	
	if($_POST['buscar'])
	{
		?>
		<br>
	
	<div class="table-responsive">
	<div class="container" id="container1" style="padding-top: 1em;">
    	<table class="table table-hover  table-condensed">
      		<thead>
				<tr>
		
					<th class="col-sm-2">Nombre</th>
					<th class="col-sm-1">Direcc</th>
					<th class="col-sm-1">Tel</th>
					<th class="col-sm-1">Fecha de ingreso</th>
					<th class="col-sm-1">Nombre trabajo</th>
					<th class="col-sm-1">Costo</th>
					<th class="col-sm-1">Unidad</th>
					<th class="col-sm-1">Total</th>
					<th class="col-sm-1">Anticipo</th>
					<th class="col-sm-1">Resta</th>
					<th class="col-sm-1">Editar</th>
					<th class="col-sm-1">Eliminar</th>
				</tr>
			</thead>
	<?php
	while($resultado = $db->obtenerfilas($consulta))
		
		{?>
	  			<tr>
					<td class="Nombre"><?=$resultado['Nombre']?></td>
					<td class="Direccion"><?=$resultado['Direccion']?></td>
					<td class="Telefonos"><?=$resultado['Telefono']?></td>
					<td class="Fecha_ingreso"><?=$resultado['Fecha_ingreso']?></td>
					<td class="Tipo_trabajo"><?=$resultado['Id_trabajo']?></td>
					<td class="Costo_uni"><?=$resultado['Costo_uni']?></td>
					<td class="Cantidad"><?=$resultado['Cantidad']?></td>
					<td clas="Total"><?=$resultado['Total']?></td>
					<td class="Anticipo"><?=$resultado['Anticipo']?></td>
					<td clas="Resta"><?=$resultado['Resta']?></td>
					
					<td class="Editar"><button type="button" class="btn btn-info" data-toggle="modal" data-target="#dataUpdate" data-id="<?php echo $row['id']?>"><i class='glyphicon glyphicon-edit'></i></button></td>
          			

          			<td class="Eliminar"><button type="button" class="btn btn-danger" data-toggle="modal" data-target="#dataDelete" data-id="<?php echo $row['id']?>"><i class='glyphicon glyphicon-trash'></i></button></td>
  
				</tr>
 	<? } ?>  <!--Cierra el while-->
		</table>
	</div> <!--Cierra el container-->
	</div>
		<br>
		<?php

		}echo "";

		?>
</form>
</div>

mysql php

asked by Root93 09.11.2016 в 06:18

source

1 answer

Nesting of WebPanels Foreach with two array

score 2 · Accepted Answer

This is clearly with a JOIN .

You can do the following query and run it on Phpmyadmin or the platform you use:

SELECT
  * 
FROM 
  regis AS R
JOIN
  trabajos AS T
ON
  R.id_trabajo = T.id

It will return all records that have the same job_id.

If you want a specific record, just add a where:

WHERE
  id_cliente = 1

By putting a value on the WHERE , you already put it in your code. For example, if the client_id you get by POST , and in summary, it would be like this:

$id_cliente = $_POST['id_cliente'];

$query = "
  SELECT
      * 
    FROM 
      regis AS R
    JOIN
      trabajos AS T
    ON
      R.id_trabajo = T.id 
 WHERE
      id_cliente = $id_cliente ";

On the other hand, if you get it from the search field then it would look like this:

$query = "
      SELECT
          * 
        FROM 
          regis AS R
        JOIN
          trabajos AS T
        ON
          R.id_trabajo = T.id 
     WHERE
          nombre LIKE '%$nombre_cliente%' ";

You execute the query, you get the result and you print it. You comment if it worked.

Note: This is an example for teaching purposes. Never use variables from the user's source without validating them, such as POST or GET values, as you may be vulnerable to SQL injection. To avoid this, use data type validations (int for id's, string for other fields, etc), if necessary, heal them ( settype or FILTER_VAR ) and use a Mysqli method called real_escape_string ; or use prepared statements.