Delete repeated records in PHP

Question

Delete repeated records in PHP

Asked 6 years, 10 months ago

Viewed 250 times

1

Good morning, I’m here with a very big question. I’ve been at this all day and I haven’t been able to fix it yet. I have a database in sql server and I need to present some buttons with the records of the description of a table, but note that in this table there are records that can be repeated, in this case I have restaurant 4 times because the restaurant has more than one table. I’ll show you the result print and leave the code here if you can help me.

<head>
  <link rel="stylesheet" type="text/css" href="css/style.css">
</head>

<?php

	 $sql="SELECT P.id as Id, 
		   P.pointOfSale as [Description], 
		   P.printerExtrat1 as PrinterExtrat1, 
		   P.printerExtrat1FonteTicket as PrinterExtrat1Font, 
		   P.printerExtrat2 as PrinterExtrat2, 
		   P.printerExtrat2FonteTicket as PrinterExtrat2Font, 
		   P.printerExtrat3 as PrinterExtrat3, 
		   P.printerExtrat3FonteTicket as PrinterExtrat3Font, 
		   T.id as IdTable, 
		   T.tableDescription as TableDescription 
		   FROM PointOfSale P LEFT 
		   JOIN PointOfSalesTables T ON P.id=T.idPointOfSale and T.isActive=1 
		   WHERE P.isActive=1 
		   ORDER BY P.pointOfSale";
		$result = sqlsrv_query($conn, $sql);
		
	while ($row1 = sqlsrv_fetch_array( $result, SQLSRV_FETCH_NUMERIC)) {
		$resultado1 = $row1[1];
		?>
		
		<body>
			<form method="post">
				<a  class="banquetes"><?php echo $resultado1; ?></a>
			</form>
		</body>
		<?php
		}
?>

I can’t show more code due to the confidential links to the database.

3 answers

Browser other questions tagged php sql-server query

You are not signed in. Login or sign up in order to post.

by Bacco • **93,720** points · Answer 1 · 2018-02-22T11:24:18+00:00

One possibility is to add this line into your query:

GROUP BY P.id

Attention to position, in SQL the clauses have specific order:

...
WHERE P.isActive=1 
GROUP BY P.id               -- ou T.idPointOfSale, claro
ORDER BY P.pointOfSale";

The GROUP BY causes the results to be (as the name already says) grouped in a row only for each occurrence of the selected field (in this case, a unique ID of each point of sale)

Adjust the above field if necessary. I did not suggest using the name because you could have 2 "restaurant" dealing with different things, so I preferred the ID. It may be that in your case the name is more appropriate. The important thing is to understand the functioning.

Note that it does not apply to your case, but the GROUP BY serves more than limit the output. It is very important for you to count, add, determine the highest or lowest record in your query.

Example:

SELECT tipo, COUNT(*), SUM( valor ) FROM tabela GROUP BY tipo

This would cause only one line of each to be shown tipo, then count how many lines each tipo, and the sum of the values only of that tipo.

Alternatively you could study the use of DISTINCT, but only knowing more details of its implementation and goal (although, internally, in many Dbs DISTINCT.is implemented as a specific case of GROUP BY, often resulting in the same implementation plan):

 SELECT  DISTINCT P.id     AS Id, 
         P.pointOfSale     AS [Description], 
         P.printerExtrat1  AS PrinterExtrat1,

by Cunha • 1 point · Answer 2 · 2018-02-22T11:24:24+00:00

0

It is possible to solve this in 2 ways, by query or by php,

With the query you can make a group by of that column, so it will not give you repeated data.

In php you can do something similar (I do not advise)

 $resultado1 = $row1[1];
 if($lastWord == $resultado1){
 //MOSTRAR HTML
 }else{
  //Ñ MOSTRAR HTML
 }
 $lastWord = $resultado1

By php did not work but I will then try group by

– Melissa Sousa

2018/02/22 at 11:27
I made a mistake, you have to change if I put it backwards, if they’re equal it doesn’t show

– Cunha

2018/02/22 at 11:29
It is still an option, but your IF would not work, it would be easier to have an array with repeated keys and use array_unique to eliminate duplicity. The however is if it is a pagination system, where N items are expected to complete a list and duplicates are removed...

– Papa Charlie

2018/02/22 at 23:37

by Cleyton Urani • 39 points · Answer 3 · 2018-02-22T12:33:09+00:00

Melissa, From what I understood from the script (I could not open the image due to firewall restrictions) the group by and/ or dictinct attempts will not work, because in return you will have something like:

Id - Description - .... - IdTable ...
1  - Restaurante1 - .... - 1
2  - Restaurante1 - .... - 3

I believe there will be differences in the columns Id, Idtable, Tabledescription ... I suggest you remove the columns you will not use in the query because with $row1[1] you only show the [Description]. And add Distinct as already mentioned.