Tag Archives: mysql

how to access Joomla 2.5!: Classes for Accessing Database for mysql

For select Query:

$db = JFactory::getDBO();
$query = $db->getQuery(true);
$query->select('field1,fieldn');
$query->from('#__tablename');
$db->setQuery((string)$query);
$results = $db->loadObjectList();
if ($results){
    foreach($results as $result) 
    {
        //$result->field1
        //$result->fieldn;
    }
}
 
For insert Query:
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->insert('#__tablename');
$query->set("field1='value1', fieldn='valuen'");
$db->setQuery($query);
$db->query();
To get the last insert id, use:
$db->insertid();


For udate Query:
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->update('#__tablename');
$query->set("field1='value1', fieldn='valuen'");
$query->where("fieldx='valuex'");
$db->setQuery($query);
$db->query();
 
For simple query: 
$db = JFactory::getDbo();
$query = "<YOUR QUERY>";
$db->setQuery($query);
$db->query();

For fetch multiple row:

Your Query goes here
$items = $db->loadObjectList();


For fetch Single row:

Your Query goes here
$book_item = $db->loadObject();


Advertisements

find array value from table in mysql

Suppose we have store data like 1,2,3,4

and find with single record like 1

than we can use select query

select * from table where id=1 .

when run query  your result set return 0 but if you can use select query with FIND_IN_SET than you will get result. like this query

select * from table where FIND_IN_SET(‘1’,id) // here id is field name.

Hope you help..cheers!.

Get last record using php or php with mysql

For example: In this example explode commas and make a linkable every word.

<?php
$ash = ‘1,2,3,4’;
$ashval = explode(‘,’,$ash);

foreach($ashval as $val)
{
print “<a href=”>”.$val.”</a>”;
if($val!=end($ashval))
{
print ‘,’;
}
else
{
print ”;
}

}
?>

OR you can use another method:

<?php
$ash = ‘1,2,3,4’;
$ashval = explode(‘,’,$ash);
$checkval=count($ashval);

foreach($ashval as $val)
{

if($checkval!=$val)
{
$te=”,”;
}
else
{
$te=””;
}
print “<a href=”>”. $val.”</a>”.$te;

}
?>

Edit data in php

php mysql basic step by step tutorial -update record: In editing the data, you can use the syntax as follow:

file: db.inc.php for connection database

<?
//file include for database connection
//db.inc.php
//database connection
mysql_connect(“localhost”,”root”,”admin”);

// database selection
mysql_select_db(“employees”);
?>

file: edit.php for connection database

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv=”Content-Type” content=”text/html; charset=iso-8859-1″>
</head>
<body>
<table>
<tr>
<td align=”center”>EDIT DATA</td>
</tr>
<tr>
<td>
<table border=”1″>
<?
include”db.inc.php”;//database connection
$order = “SELECT * FROM data_employees”;
$result = mysql_query($order);
while ($row=mysql_fetch_array($result)){
echo (“<tr><td>$row[name]</td>”);
echo (“<td>$row[employees_number]</td>”);
echo (“<td>$row[address]</td>”);
echo (“<td><a href=\”edit_form.php?id=$row[employees_number]\”>Edit</a></td></tr>”);
}
?>
</table>
</td>
</tr>
</table>
</body>
</html>

The picture above is edit.php file where this file will show overall data in the table, then there is edit menu in the last column. If you click the edit menu, it will bring the program to execute edit_form.php file. Edit_form.php file will show a form to edit the data which have been selected in the previous form. The mechanism is that the user choose one of the data that will be edited in the first form (edit.php file) by clicking the edit menu in the right column. You can see edit_form.php program file as follow:

<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN”
http://www.w3.org/TR/html4/loose.dtd”&gt;
<html>
<head>
<title>Form Edit Data</title>
</head>

<body>
<table border=1>
<tr>
<td align=center>Form Edit Employees Data</td>
</tr>
<tr>
<td>
<table>
<?
include “db.inc.php”;//database connection
$order = “SELECT * FROM data_employees
where employees_number=’$id'”;
$result = mysql_query($order);
$row = mysql_fetch_array($result);
?>
<form method=”post” action=”edit_data.php”>
<input type=”hidden” name=”id” value=”<? echo “$row[employees_number]”?>”>
<tr>
<td>Name</td>
<td>
<input type=”text” name=”name”
size=”20″ value=”<? echo “$row[name]”?>”>
</td>
</tr>
<tr>
<td>Address</td>
<td>
<input type=”text” name=”address” size=”40″
value=”<? echo “$row[address]”?>”>
</td>
</tr>
<tr>
<td align=”right”>
<input type=”submit”
name=”submit value” value=”Edit”>
</td>
</tr>
</form>
</table>
</td>
</tr>
</table>
</body>
</html>

By clicking the edit button, the program goes to the fourth program, edit_data.php file, which brings three variable such as $id variable which contains of employees number data, $name variable which contains of employees name data, and $address variable which contains of employees address. In order to know whether the data is already change or not, the program is re-instructed to edit.php file with the order of header (“location:edit.php”). Here is the edit_data.php program file:

<?
//edit_data.php
include “db.inc.php”;
$order = “UPDATE data_employees
SET name=’$name’,
address=’$address’
WHERE
employees_number=’$id'”;
mysql_query($order);
header(“location:edit.php”);
?>