[RESOLVED] Linking tables via php

Kleidi

Hello everyone!
I'm new on php/mysql dev and i'm looking around on a way to link 2 mysql tables and show them up. What i have now is only those 2 tables on mysql:
1. p_category - Id , Name, Permalink
2. p_product - Id, Name, Permalink, Info, Img

When add a product, that product should correspond to a category (ex: product "LCD TV" should be on category "Electronics").
What i need now is a way how to do this. Can anyone help me, PLEASE?

Thank you!

laserlight

If a product can only belong to one category, then you should add a foreign key to p_product that corresponds to p_category.Id. Obtaining the name of the product's category can then be accomplished by a join of p_product and p_category on this foreign key.

If a product can belong to more than one category, then you should create a new table with two foreign key columns: one for p_category and another for p_product. Obtaining the names of the product's categories can then be accomplished by a join of all three tables, on these foreign keys.

Kleidi

laserlight;10950036 wrote:
If a product can only belong to one category, then you should add a foreign key to p_product that corresponds to p_category.Id. Obtaining the name of the product's category can then be accomplished by a join of p_product and p_category on this foreign key.

If a product can belong to more than one category, then you should create a new table with two foreign key columns: one for p_category and another for p_product. Obtaining the names of the product's categories can then be accomplished by a join of all three tables, on these foreign keys.

A product can belong to one category only.
Any example on how to do what u suggest, please?

laserlight

Something like this:

SELECT p_product.*, p_category.Name AS CategoryName
    FROM p_product LEFT JOIN p_category ON p_product.CatId=p_category.Id

where CatId is the foreign key name.

Kleidi

Aha, that mean that i should add a new field in p_product named Catid?
I will try and update.
Thank you very much 😉

Kleidi

I have tried in many ways but i'm not getting what i need 🙁

My db tables looks like this:

p_kategori table

[CODE]

-- Table structure for table `p_kategori`

CREATE TABLE IF NOT EXISTS p_kategori (
id int(6) NOT NULL auto_increment,
katemri varchar(255) NOT NULL default '',
katpermalink varchar(255) NOT NULL default '',
kataktiv enum('N','Y') NOT NULL default 'Y',
PRIMARY KEY (id),
UNIQUE KEY id (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

-- Dumping data for table `p_kategori`

INSERT INTO p_kategori (id, katemri, katpermalink, kataktiv) VALUES
(1, 'Gjeneratore', 'gjeneratore/', 'Y'),
(2, 'Elektroda', 'elektroda/', 'Y'),
(3, 'Test', 'test/', ''),
(4, 'Test2', 'test2/', ''),
(5, 'Prove', 'prove/', ''),
(6, 'Prove 2', 'prove2/', ''),
(7, 'Prove 3', 'prove3/', 'Y'),
(8, 'Test 4', 'test4/', 'Y');

[/CODE]

p_produkte table

[CODE]

-- Table structure for table `p_produkte`

CREATE TABLE IF NOT EXISTS p_produkte (
id int(6) NOT NULL auto_increment,
katid varchar(255) NOT NULL default '',
prodemri varchar(255) NOT NULL default '',
prodimg varchar(255) NOT NULL default '',
prodinfo text NOT NULL,
prodpermalink varchar(255) NOT NULL default '',
prodaktiv enum('N','Y') NOT NULL default 'Y',
PRIMARY KEY (id),
UNIQUE KEY id (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

-- Dumping data for table `p_produkte`

INSERT INTO p_produkte (id, katid, prodemri, prodimg, prodinfo, prodpermalink, prodaktiv) VALUES
(2, '1', 'XCV 220W', '220w.jpg', '<p>\r\n Gjenerator</p>\r\n', 'gjenerator_xcv220w/', 'Y');
[/CODE]

kategorite.php - The page where appear the category list and, if clicks on a category, will appear a new page where should be listed all products on that category.


<?php
if (!isset($_GET['id'])) // if $_GET['id'] is NOT set
{
	include 'D:/Program Files/VertrigoServ/www/joni/admini/includet/variabla.php';
	include (BPATH . '/includet/dbconfig.php');
	$query="SELECT * FROM `p_kategori` WHERE kataktiv = 'Y' ORDER BY `p_kategori`.`ID`";
	$result=mysql_query($query);
	$num=mysql_numrows($result);
	mysql_close();

$i=0;
   while ($i < $num) {
   $id=mysql_result($result,$i,"id");
   $katemri=mysql_result($result,$i,"katemri");
   $katpermalink=mysql_result($result,$i,"katpermalink");
   $kataktiv=mysql_result($result,$i,"kataktiv");
  ?>

<img src="imazhet/bulona.jpg" width="15" hspace="5" vspace="3" align="absmiddle" /><a href="kategorite.php?id=<?php echo $id;  ?>"> <?php echo $katemri;  ?></a><br>

<?php
   $i++;
   }
   ?>	



<?php
} // end if()
else // $_GET['id'] IS set
{
   if (!empty($_GET['id'])) // if $_GET['id'] is NOT empty
   {


 // getting infos from db
  include 'D:/Program Files/VertrigoServ/www/joni/admini/includet/variabla.php';
 include (BPATH . '/includet/dbconfig.php');
   $query="SELECT * FROM `p_kategori` WHERE id = '$_GET[id]'";
   $result=mysql_query($query);
   $num=mysql_numrows($result);

   $id=mysql_result($result,$i,"id");
   $katemri=mysql_result($result,$i,"katemri");
   $katpermalink=mysql_result($result,$i,"katpermalink");
   $kataktiv=mysql_result($result,$i,"kataktiv");
   // end of getting infos from db
?>


...
...
...

I don't know how to continue now!

...
...
...





<?php     

   } // end if()
} // end else
?>

I don't know how to continue now 🙁 ! I have tried to use the query you suggest but i't didn;t worked bcz i don;t know how and where to use it.

Can you please help me?

Thank you in advance!

niroshan

Hi Kleidi,

Before giving you the code i like to point out some of the issues i see in your code,

CREATE TABLE IF NOT EXISTS p_produkte (
id int(6) NOT NULL auto_increment,
katid varchar(255) NOT NULL default '',
......

Why did you create the katid as varchar. katid is the foreign key to id field of the p_kategori table. since it holding a integer value it also should be an integer type.

include 'D:/Program Files/VertrigoServ/www/joni/admini/includet/variabla.php';

Always try to use the relative path since, assume if you upload this into a real server then this path doesn`t exists.

include (BPATH . '/includet/dbconfig.php');

This is the correct why assuming BPATH having the full path.

$id=mysql_result($result,$i,"id");
$katemri=mysql_result($result,$i,"katemri");
$katpermalink=mysql_result($result,$i,"katpermalink");
$kataktiv=mysql_result($result,$i,"kataktiv");

Instead try mysql_fetch_assoc and this is what php manual has to say about it.

When working on large result sets, you should consider using one of the functions that fetch an entire row (specified below). As these functions return the contents of multiple cells in one function call, they're MUCH quicker than mysql_result(). Also, note that specifying a numeric offset for the field argument is much quicker than specifying a fieldname or tablename.fieldname argument.

mysql_fetch_row() - Get a result row as an enumerated array

mysql_fetch_array() - Fetch a result row as an associative array, a numeric array, or both

mysql_fetch_assoc() - Fetch a result row as an associative array

mysql_fetch_object() - Fetch a result row as an object

And now to the code,

<?php
include_once(BPATH . 'includet/variabla.php');
include_once(BPATH . '/includet/dbconfig.php'); 

//get the list of categories
$sql="SELECT katemri, id FROM p_kategori WHERE kataktiv = 'Y' ORDER BY id";
$result=mysql_query($sql);
while($row = mysql_fetch_assoc($result)) //loop through the categories and display them
{
?>
	<p><img src="imazhet/bulona.jpg" width="15" hspace="5" vspace="3" align="absmiddle" /><a href="test2.php?id=<?php echo $row['id'];  ?>"> <?php echo $row['katemri'];  ?></a></p> 
  <?php	
}

if(isset($_GET['id'])) //Check if user clicks on a perticular category
{
?>
	<table width="100%" border="0">
	  <tr style="font-weight:bold">
	    <td>ID</td>
	    <td>Category</td>
	    <td>Product</td>
      </tr>
<?php	
	$catId = intval($_GET['id']); //make sure its an integer
	$sql="SELECT B.katemri, A.id AS proid, A.prodemri AS proname FROM p_produkte A LEFT JOIN p_kategori B ON A.katid = B.id WHERE B.id = ".$catId;
	$result=mysql_query($sql);
	if(mysql_num_rows($result) > 0) //products found
	{
		while($row = mysql_fetch_assoc($result)) //Loop through the procusts and display them
		{
?>		
            <tr>
                <td><?php echo $row['proid']; ?></td>
                <td><?php echo $row['katemri']; ?></td>
                <td><?php echo $row['proname']; ?></td>
            </tr>
<?php	
		}
	}
	else //no products found
	{
?>		
        <tr>
            <td colspan="3">No Products</td>
        </tr>
<?php			
	}
?>
	</table>
<?php    

}
?>

Hope this helps,

Thanks,
best regards,
Niroshan

Kleidi

Why did you create the katid as varchar. katid is the foreign key to id field of the p_kategori table. since it holding a integer value it also should be an integer type.

I'm new on this and i didn't know that it should be an integer. I have modified it from varchar to int.

Always try to use the relative path since, assume if you upload this into a real server then this path doesn`t exists.

I use

include 'D:/Program Files/VertrigoServ/www/joni/admini/includet/variabla.php';

bcz BPATH variable is defined at variabla.php file and if i will not use this type of include, BPATH will not work.

Instead try mysql_fetch_assoc and this is what php manual has to say about it.

Hmm... looks great in this way instead of defining all of those variables 😉 Thx... I will read that article.

About code, i have insterted it on my page and it works 😉 thanks to you. What i don't like is that, when i click on any category, the category list does not disappear but it stays there, on top. Can you, PLEASE, suggest me a way to do rearrange it or make it disappear when someone click on any category?

Thank you very very very much for your help.

niroshan

Hi Kleidi,

bcz BPATH variable is defined at variabla.php file and if i will not use this type of include, BPATH will not work.

In this case what you can do is define the path from root. (Im assuming variabla.php file is in the root includet folder)

include_once('/includet/variabla.php');

What i don't like is that, when i click on any category, the category list does not disappear but it stays there, on top

It doesnt disappear because we havent told PHP to make it disappear when you click on a category. To do this simply put the category display code inside a else statement.

if(isset($_GET['id'])) //Check if user clicks on a perticular category 
{
    //display products
}
else
{
  //display categories
}

I dont wanted to put the code so you can try your self and learn 😃

Good luck,

Best regards,
Niroshan

Kleidi

Thank you for your reply and for helping me...
I was looking around on the code you gave me at the first post, but i dunno if i should place the category display code at the existing else statement or i should make a new else statement 😉

Thank again!

niroshan

Hi Kleidi,

If you look at the code closely you`ll understand that the existing else is for, when ever there is no products found for the selected category.

so as for your requirement you have to create a new else statement and place the category listing code inside that else statement. Try your self. If you have very hard timing figuring this out i will write you the code 🙂

Take a very close a look at this example i gave you.

if(isset($_GET['id'])) //Check if user clicks on a perticular category 
{ 
    //display products 
} 
else 
{ 
  //display categories 
}

Regards,
niroshan

Kleidi

Eh, i was in doubt of that else, bcz i saw that the existing else statement was for products. I will "play" with that and update you.
Thank you 😉

Kleidi

DONE ... Thanks to you 😉

My code looks like this now:

kategorite.php (first page that is located on root)

<?php
	include 'D:/Program Files/VertrigoServ/www/joni/admini/includet/variabla.php';
	include (BPATH . '/includet/dbconfig.php');
if(isset($_GET['id'])) //Check if user clicks on a perticular category
{
?>

<!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">
<!--Fillon: Seksioni Head për faqet -->
<head>
	<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
	... META TAGS ...
	<link href="<?php echo $url; ?>stildizenjimi.css" rel="stylesheet" type="text/css" />
	<link rel="shortcut icon" href="favicon.ico" >
</head>

<!--Mbaron: Seksioni i Head për Faqet-->


<!--Fillon: Trupi i Faqeve -->

<body>
 <?php include (BPATH . '/includet/koka.php'); ?>

<div class="trupi" align="center">       

<div class="pastro"></div>

  <div class="qender">
        </div>




    <?php include (BPATH . '/includet/kategorite.php'); ?> <!--The file where are located the rest of the code you give to me ;) -->



   <?php include (BPATH . '/includet/majtas.php'); ?>





    <div class="pastro"></div>
</div></div>
<!-- Mbaron: Trupi i Faqeve-->
 <?php include (BPATH . '/includet/fundi.php'); ?>
</body>
</html>
<?php    

}
else
{


//get the list of categories
$sql="SELECT katemri, id FROM p_kategori WHERE kataktiv = 'Y' ORDER BY id";
$result=mysql_query($sql);
while($row = mysql_fetch_assoc($result)) //loop through the categories and display them
{
?>
<img src="imazhet/bulona.jpg" width="15" hspace="5" vspace="3" align="absmiddle" /><a href="kategorite.php?id=<?php echo $row['id'];  ?>"> <?php echo $row['katemri'];  ?></a><br />
  <?php    

}

} 
?>

includet/kategorite.php


<!--Fillon: Mdouli Djathtas-->
<div class="djathtas">
	<div class="titullfaqe">



		<div class="titulltxt">Ne Ju Ofrojme...</div> <!--Insted of this text i want to use <?php echo $row['katemri']; ?> but the category name does not appear ...-->





</div>
<div class="pastro"></div>

<div class="txtkategorite"><br />

<table width="100%" border="0" class="txtkategorite">
	<td width="85"><b>Kategoria</b></td>
    <td width="150"><b>Emri</b></td>
    <td width="270"><b>Informacione</b></td>
    <td width="200"><b>Fotografia</b></td>
   </tr>
<?php    

    $catId = intval($_GET['id']); //make sure its an integer
    $sql="SELECT B.katemri, A.id AS prodid, A.prodimg AS prodimg, A.prodinfo AS prodinfo, A.prodemri AS prodemri FROM p_produkte A LEFT JOIN p_kategori B ON A.katid = B.id WHERE B.id = ".$catId;
    $result=mysql_query($sql);
    if(mysql_num_rows($result) > 0) //products found
    {
        while($row = mysql_fetch_assoc($result)) //Loop through the procusts and display them
        {
?>        

            <tr>
            	<td><?php echo $row['katemri']; ?></td>
                <td><?php echo $row['prodemri']; ?></td>
                <td><?php echo $row['prodinfo']; ?></td>
                <td><?php echo $row['prodimg']; ?></td>
            </tr>
<?php    

        }
    }
    else //no products found
    {
?>        

        <tr>
            <td colspan="3">Nuk ka asnje produkt ne kete kategori!</td>
        </tr>
<?php            

    }
?>
    </table>

</div>

</div>

<!--Mbaron: Mdouli Djathtas-->

And works great thanks to you. What i want to do now is to use

<?php echo $row['katemri']; ?>

instead of

<div class="titulltxt">Ne Ju Ofrojme...</div>

but it didn't worked.

I moved

?php    

    $catId = intval($_GET['id']); //make sure its an integer
    $sql="SELECT B.katemri, A.id AS prodid, A.prodimg AS prodimg, A.prodinfo AS prodinfo, A.prodemri AS prodemri FROM p_produkte A LEFT JOIN p_kategori B ON A.katid = B.id WHERE B.id = ".$catId;
    $result=mysql_query($sql);
    if(mysql_num_rows($result) > 0) //products found
    {
       ?>

at the top of the page, but it mess my design where clicks on a category that does not have any products on it. Any trick 😉

Thanks again 😉 !

niroshan

Hi Kleidi,

GREAT WORK 😃

but I have some small issues i like to point out 🙂

if(isset($_GET['id'])) //Check if user clicks on a perticular category
{
?>
 <!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">
    <!--Fillon: Seksioni Head për faqet -->
    <head>
    <meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
    ... META TAGS ...
     . .
     .
     .

why did you put the <html>, <header> ...etc inside the if condition? because assume your coming to the site first time and it only list the categories right? now try to go to view -> page source (on FF). see the output... thats not a correct html page isn`t it? try to put all the html code out side the condition and only keep the relevant body part inside the condition.

eg:

<!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">
<!--Fillon: Seksioni Head për faqet -->
<head>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
... META TAGS ...
<link href="<?php echo $url; ?>stildizenjimi.css" rel="stylesheet" type="text/css" />
<link rel="shortcut icon" href="favicon.ico" >
</head><!--Mbaron: Seksioni i Head për Faqet-->
<!--Fillon: Trupi i Faqeve -->
<body>
<?php include (BPATH . '/includet/koka.php'); ?>
<div class="trupi" align="center">
  <div class="pastro"></div>
  <div class="qender"> </div>
  <?php
  if(isset($_GET['id'])) //Check if user clicks on a perticular category
  {
  	include (BPATH . '/includet/kategorite.php');
  }
  else
  {
  	 //get the list of categories
	$sql="SELECT katemri, id FROM p_kategori WHERE kataktiv = 'Y' ORDER BY id";
	$result=mysql_query($sql);
	while($row = mysql_fetch_assoc($result)) //loop through the categories and display them
	{
	?>
		<img src="imazhet/bulona.jpg" width="15" hspace="5" vspace="3" align="absmiddle" /><a href="kategorite.php?id=<?php echo $row['id'];  ?>"><?php echo $row['katemri'];  ?></a><br />
	<?php    

	}
  }
  include (BPATH . '/includet/majtas.php');
  ?>
  <div class="pastro"></div>
</div>
</div>
<!-- Mbaron: Trupi i Faqeve-->
<?php include (BPATH . '/includet/fundi.php'); ?>
</body>
</html>

By the way instead of in including 'kategorite.php' why dont you put the code there it self 😉

Now i`ll come to your original question 🙂

As you can see in the kategorite.php file (the root file) before your including the /includet/kategorite.php file you dont do any database queries.So thats means $row variable didnt even exists. so if you want to display the selected category name inside that include file you have to first query the databse and obtain that value.

so what you can do is this,

//includet/kategorite.php

<div class="titulltxt">Ne Ju Ofrojme...</div>
    <?php
    $catId = intval($_GET['id']); //make sure its an integer
	$sql = "SELECT katemri FROM p_kategori WHERE id =".$catId; //get the selected category name
	$result=mysql_query($sql);
	$row = mysql_fetch_assoc($result);
	echo $row['katemri']; //display the category name
	?>
  </div>

Hope this helps.

Best regards,
niroshan

Kleidi

Hello there ... I don;t know how to thank you 😉

About how looks the source, it's looking great from my end 😉

Anyway, i'm having trouble with the edit page 🙁 It don;t want to update the raw on submit. When i click on edit link, appear the page with all existent variables and after editing, submit the form, but the modifications is not made in the database. The msg after submit is "Succesfully updated".

modprodukte.php

<?php


session_start();

if(!isset($_SESSION['loggedin'])) {
   header('Location: '.$domain.'index.php?error=1');
   exit();
}

define("NGA_ADMIN", true);
?>
<?php
include 'D:/Program Files/VertrigoServ/www/joni/admini/includet/variabla.php';
include (BPATH_ADM . 'includet/dbconfig.php');
$id = $_GET['id'];

$qProfile = "SELECT * FROM p_produkte WHERE id='$id'  ";
$rsProfile = mysql_query($qProfile);
$row = mysql_fetch_array($rsProfile);
extract($row);
$prodemri = stripslashes($prodemri);
$prodinfo = stripslashes($prodinfo);
$prodimg = stripslashes($prodimg);
$prodaktiv = stripslashes($prodaktiv);
$katid = stripslashes($katid);

mysql_close();
?>
<form id="modproduktet" action="modulet/produktet/p.modprodukte.php" method="post" name="modproduktet">
<table width="740" border="0" cellspacing="2" cellpadding="0">
<tr><td width="50"><div align="right">
<label for="prodemri">Emri</label></div>
</td>
<td>
<input id="prodemri" name="prodemri" type="text" size="25" value="<?php echo $prodemri ?>" maxlength="255"></td>
</tr>



<tr><td width = "150"><div align="right"><label for="katid">Kategoria</label></div></td>
<td><select id="katid" name="katid" size="1">
<?php
include 'D:/Program Files/VertrigoServ/www/joni/admini/includet/variabla.php';
include (BPATH_ADM . 'includet/dbconfig.php');

$query="SELECT * FROM p_kategori ";
$result=mysql_query($query);
$num = mysql_num_rows ($result);
mysql_close();
?>

<?php
if ($num > 0 ) {
$i=0;
while ($i < $num) {
$katemri = mysql_result($result,$i,"katemri");
$katpermalink = mysql_result($result,$i,"katpermalink");
$kataktiv = mysql_result($result,$i,"kataktiv");
$id = mysql_result($result,$i,"id");
?>

<option value="<?php echo $id; ?>"><?php echo $katemri; ?></option>

<?php
++$i; } }
?>
</select></td></tr>



<tr><td width="50"><div align="right">
<label for="prodpermalink">Linku</label></div>
</td>

<tr><td width="50"><div align="right">
<label for="prodinfo">Info</label></div>
</td>
<td>
<textarea id="prodinfo" name="prodinfoteksti" rows="4" cols="40"><?php echo $prodinfo ?></textarea>
<script type="text/javascript">
	CKEDITOR.replace( 'prodinfo',
    {
        filebrowserBrowseUrl : '/joni/admini/includet/ckfinder/ckfinder.html',
        filebrowserImageBrowseUrl : '/joni/admini/includet/ckfinder/ckfinder.html?Type=Images',
        filebrowserFlashBrowseUrl : '/joni/admini/includet/ckfinder/ckfinder.html?Type=Flash',
        filebrowserUploadUrl : '/joni/admini/includet/ckfinder/core/connector/php/connector.php?command=QuickUpload&type=Files',
        filebrowserImageUploadUrl : '/joni/admini/includet/ckfinder/core/connector/php/connector.php?command=QuickUpload&type=Images',
        filebrowserFlashUploadUrl : '/joni/admini/includet/ckfinder/core/connector/php/connector.php?command=QuickUpload&type=Flash'
    });
</script>
</td>
</tr>


<tr><td width="50"><div align="right">
<label for="prodimg">Fotografia</label></div>
</td>
<td>
<input id="prodimg" name="prodimg" type="text" size="25" value="<?php echo $prodimg ?>" maxlength="255"></td>
</tr>

<tr><td width="50"><div align="right">
<label for="prodaktiv">Aktive</label></div>
</td>
<td>
<input id="prodaktiv" name="prodaktiv" type="checkbox" <?php if ($prodaktiv == "Y") { echo "checked";} ?> value="Y"></td>
</tr>
<tr>
<td width="50"></td>
<td><input type="submit" name="Modifiko" value="Modifiko"><input type="hidden" name="id" value="<?php echo $id ?>"></td>
</tr>
</table>
</form>

p.modprodukte.php


<?php


session_start();

if(!isset($_SESSION['loggedin'])) {
   header('Location: '.$domain.'index.php?error=1');
   exit();
}

define("NGA_ADMIN", true);
?>

<form method="post">
<input type="button" value="Mbylle Dritaren"
onclick="window.close()">
</form>
<?php
include 'D:/Program Files/VertrigoServ/www/joni/admini/includet/variabla.php';
include (BPATH_ADM . 'includet/dbconfig.php');

$id = $_POST['id'];
$prodemri = $_POST['prodemri'];
$prodinfo = $_POST['prodinfo'];
$prodimg = $_POST['prodimg'];
$katid = $_POST['katid'];
$prodaktiv = $_POST['prodaktiv'];

$update = "UPDATE  p_produkte SET prodemri = '$prodemri', prodinfo = '$prodinfo', prodimg = '$prodimg', katid = '$katid', prodaktiv = '$prodaktiv' WHERE  id ='$id' LIMIT 1 ";
$rsUpdate = mysql_query($update);
if ($rsUpdate)
{
echo "Modifikimi u krye me sukses.";
} mysql_close();
?>

database p_produkte

        Field  	Type  	
	id 	         int(6) 
	katid 	int(255)
	prodemri 	varchar(255) 
	prodimg 	varchar(255)
	prodinfo 	text 	
	prodaktiv 	enum('N', 'Y')

database p_kategori

        Field  	Type  	
	id 	         int(6) 
	katemri 	varchar(255)
	katpermalink 	varchar(255) 
	kataktiv 	enum('N', 'Y')

Thank you very very very much 😉

niroshan

Hi Kleidi,

Before explain you the issue I like to point out some of the extra issues i found in your code 🙂
Biggest of them is that your script is vulnerable for SQL injection attacks.
Ok here goes the list,

$id = $_GET['id'];

Always do not trust the users. So if you are expecting $_GET['id'] to be an integer value make sure you convert that into an integer before proceeding.

$id = intval($_GET['id']);

$row = mysql_fetch_array($rsProfile);
extract($row);

This is not exactly an issue. Always try to use mysql_fetch_assoc because compared to mysql_fetch_array it is much faster.Please google to find out why exactly mysql_fetch_assoc is much faster than mysql_fetch_array 😃

Do not use mysql_close() in every query instead do it at the end of the script. Because connecting and disconnecting in every query is a overhead for the server.

include 'D:/Program Files/VertrigoServ/www/joni/admini/includet/variabla.php';
include (BPATH_ADM . 'includet/dbconfig.php');

Try to include the config file only once (usually at the begging of the file)

p.modprodukte.php

$prodinfo = $_POST['prodinfo'];

Your using the textarea ID instead use the name 🙂

$update = "UPDATE  p_produkte SET prodemri = '$prodemri', prodinfo = '$prodinfo', prodimg = '$prodimg', katid = '$katid', prodaktiv = '$prodaktiv' WHERE  id ='$id' LIMIT 1 ";

This is where the sql injection happens. Never use $_POST data directly inside the query. Use mysql_real_escape_string() in all data.

There is no LIMIT in UPDATE statements.

OK now to the error 🙂

Initially your assigning the product id which was return from a $_GET array into a variable called $id right? and your using that $id into a hidden field to pass onto the next page. but look what happen in the middle of the modprodukte.php page.

while ($i < $num) {
$katemri = mysql_result($result,$i,"katemri");
$katpermalink = mysql_result($result,$i,"katpermalink");
$kataktiv = mysql_result($result,$i,"kataktiv");
$id = mysql_result($result,$i,"id"); //----------------------------> HERE

your using the same $id variable here so the initial value will be changed here. So when you pass on to the next page you have a different ID and query is not failing because it anyway having a value for $ID but the record never gets updated. because its updating a different product 🙂

Hope this is clear.
Best regards,
Niroshan

bradgrafelman

Just to comment on niroshan's comments (hooray for nested comments! :p):

niroshan wrote:
Always try to use mysql_fetch_assoc because compared to mysql_fetch_array it is much faster.Please google to find out why exactly mysql_fetch_assoc is much faster than mysql_fetch_array

Since the difference between those two is likely to be less than 1 millisecond (unless you're retrieving large amounts of data), I'd love to know what kind of applications you develop where a time saving of a millisecond (or two) is "much faster" ! :p

Otherwise, I agree with niroshan's suggestion - there's no reason to duplicate the data in the array by using a function that creates both numeric and associative array indexes.

niroshan wrote:
Try to include the config file only once (usually at the begging of the file)

For config files and the like, I almost always use the _once versions of include or require, e.g. [man]include_once[/man].

niroshan wrote:
Use mysql_real_escape_string() in all data.

Well not on all data... only on string data. Though yes, all data must be sanitized before being placed in a SQL query.

niroshan wrote:
There is no LIMIT in UPDATE statements.

Says who? Not sure which DBMS you're working with, but standard SQL allows UPDATE queries to use a LIMIT clause.

laserlight

bradgrafelman wrote:
Says who? Not sure which DBMS you're working with, but standard SQL allows UPDATE queries to use a LIMIT clause.

If I am not wrong, standard SQL has no LIMIT clause.

bradgrafelman

laserlight;10950368 wrote:
If I am not wrong, standard SQL has no LIMIT clause.

Woops, you're right - looks like I Google'd too quickly. At any rate, many DBMS's (including MySQL) do support a LIMIT clause in an UPDATE query.

EDIT: After further Google'ing, apparently there aren't as many DBMS's as I originally thought that support a LIMIT clause in an UPDATE query. Guess it's been too long since I've even touched another DBMS besides MySQL. :p

niroshan

bradgrafelman;10950369 wrote:
EDIT: After further Google'ing, apparently there aren't as many DBMS's as I originally thought that support a LIMIT clause in an UPDATE query. Guess it's been too long since I've even touched another DBMS besides MySQL. :p

Well your kind a right. because i read through the mysql manual and it says that we can have LIMIT in UPDATE queries. And this is what it has to say about it.

Originally from mysql site

You can use LIMIT row_count to restrict the scope of the UPDATE. A LIMIT clause is a rows-matched restriction. The statement stops as soon as it has found row_count rows that satisfy the WHERE clause, whether or not they actually were changed.

but this is insane 😃 who doesn`t want to update all the records which matches the where clause 😃

best regards,
niroshan