PHP - Database Query Help

I have a test database set up that I am working on. Right now, just some names like so

firstName
lastName
companyName

I have used phpmyadmin to insert names in the database. Right now just three names are inserted. One first and last name, one first name and one company name

I am getting the results back in an associative array. How can I echo out the information with the break tag, or one field at a time? if I use a break tag, then the loop adds in extra blank lines when it gets to the first name and it's null. Can I test for null values and do it that way? I am a beginner so I have no idea what I am doing.

Code: [Select]
$result = $mysql->query("select * from names") or die($mysql->error);
if($result){
while($row = $result->fetch_assoc()){
$name = $row['firstName'];
$lastName = $row['lastName'];
$company = $row['companyName'];

echo $name . " ";
echo $lastName . " ";
echo $company . " ";
}

}

The title may not fit but only thing i could think of.

I am pulling data from MSSQL database table.... I need everything from that table but i need to do some basic math funcitons to the resulting data.

So the quesiton is should i do the math for this in the SQL query if so how do i pull all fields and do the math in an effcient way.

Or should i let php do the math for me after i get all the data i need. 

If it is better to use php how can i do this in a while loop and get the math result and not a printed equation.

Just looking for a few pointers never had to do math before with php

I'm having a problem with adding icon/avatar to my database..this is a submit form where I just submit the avatar url. I don't know how to change the submit link from avatar_add.php?mode=submit to index.php?mode=submit&x=avatar_add.
http://www.graphics.allmerk-noplay.com/index.php?x=avatar_add


Code: [Select]
<?php
$host =""; // host name
$user =""; // user
$pass =""; // password
$dbase =""; // database name

// connect to the server
mysql_connect("$host","$user","$pass");
// and select the database
mysql_select_db($dbase);

$mode=$_GET["mode"]; // get the mode
$QUERY_STRING = 'x=avatar_add';

if(!isset($mode) || empty($mode)) // if mode is empty, switch to case index
{
$mode='index';
}

switch($mode)
{

case 'index'; // displays the form
?>


<form method="post" action="avatar_add.php?mode=submit">
Avatar URL:<br>
<input type="text" name="avatar_url" size="30"><br><br>
<input type="submit" value="Submit">


<?php
break;

case 'submit'; // form processing mode

$avatar_url=$_POST["avatar_url"];

// check for empty fields
if(empty($avatar_url))

{
echo "Error! Please go back and fill in all the required fields!";
// if you have a footer include, insert it before the die statement
die; // stops the script from executing the rest of the code
}

// let's get the date in YYYY-MM-DD format
$date = date("Y-m-d");

$query="INSERT into icons_db (id,avatar_url) VALUES ('','$avatar_url')";
$result=mysql_query($query) or die ("Couldnot execute query: $query." . mysql_error());

if($result) // if mysql query successful
{ echo "Thank you! The avatar has been added to or database."; }

break;

}

?>

in database, I have faced error where query does not responded properly it was showing the error message that "too much query, user cannot execute more query".... when I increased the limit, it works for sometime and after sometime, it again generates the error , my question here is: "why the error is occurring ?" "what is the correct way to improve the execution of multiple queries at single instance ??? "

So I'm creating a website for bar deals... I've tried for hours trying to get the url to take in more than one parameter with no luck, I don't know if my database isn't setup correctly or something but the url would look like this

http://www.site.com/?id=1&day=Monday

and when those parameters are passed the deals for that id and day are then posted in the content area

I'm pretty much stumped. I feel like it's much easier than I am making it.

My database looks like:

ID   Name  Monday           Tuesday        Wednesday -> and so on

1    Bar1   MondayDeal TuesdayDeal WednesdayDeal
2    Bar2   MondayDeal -> and so on

Hi guys, i am currently working on a project that queries an inventory database through the use of radio buttons, for example; If the first button FOR THE Item(HAMMERS)is clicked, the output should be the name of the item, the number sold and the total profit(calculations) into a table. the way i have it now, when io click on the radio button my output comes back as the results for all the items in the table, i want it to display only the information about hammers, then only about each individual item when the corresponding radio button is clicked. any help would be greatly appreciated!!!

Hi
I'm using a couple of queries to search a database.
This works well but is slow as I'm cascading/nesting the queries..

Below is an example of what I mean... (LDAP queries)
Code:

Code: [Select]
<?
   $dn = "cn=user";
     $filter="(objectclass=user)";
     $justthese = array('cn','telephone','guid','ID','email');
     $sr=ldap_search($ds, $dn, $filter, $justthese);
     $info = ldap_get_entries($ds, $sr);
        for ($n=0; $n<$info['count']; $n++) {
$cn = $info[$n]['cn'][0];
$email = $info[$n]['email'][0];
$id= $info[$n]['id'][0];

   $dn = "cn=Device";
     $filter="(objectclass=device)(userid=$id)";
     $justthese = array('cn','system','guid','ID','userid');
     $sr=ldap_search($ds, $dn, $filter, $justthese);
     $info = ldap_get_entries($ds, $sr);
        for ($n=0; $n<$info['count']; $n++) {
$system = $info[$n]['system'][0];
$userid = $info[$n]['userid'][0];

    }
      }
?>
I'm sure there's a better way of writing this... that will make it faster.

Thanks

Last Christmas my wife bought me a book on html and css because I said that I have always wanted to write a website. Over the last 10 months I have really enjoyed learning web design and have progressed to try and learn php and using myPHPadmin databases.   I am now trying to write a private message function.

I've got a members database and a conversation database. I am trying to filter out whether a private message is 'read' or 'unread', but whatever I try it is still listing all messages under both options. I've spent 48 hours staring at this bit of code. Can anyone see what I have done wrong?

The relevant (and self-explanatory) fields from my members database a
id
username
member_first_name
member_last_name

The relevant fields from my conversation database a
id
id2 (always set as '1' to count messages in a conversation)
member1 (the initial sender of the first message)
member2 (the initial recipient of the first message)
read1 (member1 has read this message default to yes when message sent and to no when message received)
read2 (member2 has read this message default to yes when message sent and to no when message received)
removed1 (member1 has deleted this message from their record)
removed2 (member2 has deleted this message from their record)
time (timestamp)
 

<?php
$query1 = DB::getInstance()->query("SELECT c1.id, c1.title, c1.time, c1.removed1, c1.removed2, count(c2.id) AS reps, 
                    m.id as memberid, m.username m.member_first_name, m.member_last_name
                    FROM conversation as c1, conversation as c2, members as m
                    WHERE ((c1.member1='{$logid}' AND c1.read1='No' AND c1.removed1='No'
                    AND m.id=c1.member2)
                    OR (c1.member2='{$logid}' AND c1.read2='No' AND c1.removed2='No'
                    AND m.id=c1.member1))
                    AND c1.id2='1' AND c2.id=c1.id
                    GROUP BY c1.id
                    ORDER BY c1.id DESC");
$query2 = DB::getInstance()->query("SELECT c1.id, c1.title, c1.time, c1.removed1, c1.removed2, count(c2.id) as reps, 
                    m.id as memberid, m.username, m.member_first_name, m.member_last_name
                    FROM conversation as c1, conversation as c2, members as m
                    WHERE ((c1.member1='{$logid}' AND c1.read1='Yes' AND c1.removed1='No'
                    AND m.id=c1.member2)
                    OR (c1.member2='{$logid}' AND c1.read2='Yes' AND c1.removed2='No' AND
                    m.id=c1.member1))
                    AND c1.id2='1' AND c2.id=c1.id
                    GROUP BY c1.id
                    ORDER BY c1.id DESC");
?>

UNREAD MESSAGES (<?php echo intval($query1->count()); ?>)
<br>
READ MESSAGES (<?php echo intval($query2->count()); ?>)

I've tried running through things in a logical path:   UNREAD MESSAGES member1 = logged in user read1 = No removed1 = No members.id = member2       OR member2 = logged in user read2 = No removed2 = No members.id = member1   READ MESSAGES member1 = logged in user read1 = Yes removed1 = No members.id = member2       OR member2 = logged in user read2 = Yes removed2 = No members.id = member1   I can't understand how if 'read1 = yes and the logged in user is member1, why they are being counted in the unread list.   Can anyone see what I have done wrong?
Edited by MartynLearnsPHP, 15 November 2014 - 08:05 AM.

Hi,

I am trying to store a sql query in a database, but every time I try to read it, it shows all the code as text. eg just print ' . $parts_row['part_number'] . '

Here is the code on the page:       

    $parts_sql = "SELECT * FROM parts WHERE part_cat = " . $category_row['cat_id'] . " ORDER BY CAST(part_a AS DECIMAL(10,2))";
  			$parts_result = mysql_query($parts_sql);
  
  			if(!$parts_result)
  			{
  				echo '<tr><td>The parts could not be displayed, please try again later.</tr></td></table>';
  			}
  			else
 			{
 
 				while($parts_row = mysql_fetch_array($parts_result))
 				{
                                               $pageformat_sql = "SELECT * FROM category_pages WHERE catpage_number = " . $category_row['cat_page'] . "";
 
                                               $pageformat_result = mysql_query($pageformat_sql);
 
 
                                                if(!$pageformat_result)
                                                {
                                                echo 'Error';
                                                }
                                                 else
                                                 {
                                                 while($pageformat_row = mysql_fetch_assoc($pageformat_result))
                                                 {
                                                   $data = $pageformat_row['catpage_table'];
                                                 echo '<table class="table2 centre" style="width:750px">
 	                                              ' . $pageformat_row['catpage_tabletitle'] . '' . $data . '';
 
 
 
                                                 }
                                                 }
 
     }}

And this is what is stored in the database table:

 <tr>
 <td class="cell left">' . $parts_row['part_number'] . '</td>
 <td class="cell centre">' . $parts_row['part_a'] . ' mm</td>
 <td class="cell centre">' . $parts_row['part_b'] . ' mm</td>
 <td class="cell centre">' . $parts_row['part_c'] . ' mm</td>
 <td class="cell centre">' . $parts_row['part_d'] . ' mm</td>
 <td class="cell centre">' . $parts_row['part_e'] . ' mm</td>
 <td class="cell centre">' . $parts_row['part_imped100'] . '</td>
 <td class="cell centre"><a href="datasheets/' . $parts_row['part_datasheet'] . '.pdf"><img border="0" src="images/pdf.jpg" alt="Download"</a></td></tr>

I would be very grateful to anyone who can help me with this.

Hey guys. 

  Wondering if you people could give me some help on a procedural problem i am having.

I want to display information from a database.  What i am hoping to do is process all of the query and connection in the background.


He is how i am doing this.

there is a form that submits to the process page with post variables.  and in that page there is a few switch statements that change the query and based on that change forwards them to a different page to display the result but the connection and variable name are not being forwarded so the table never gets processed.

It is all done in an if statement and switch statemenst  any ideas on how to do this properly.  I think i am just confused on the procedure of it all.

Hello every body....   I'm creating a simple php project where the user will be able to choose option from dropdown menu and the query will be executed accordingly..   The html form is shown below....

<select name='movie_name'>
<option value='ALL'>All</option>
<option value='blah'>Blah blah </option>
<option value='blah'>Blah blah </option>
...
</select>

<select name='movie_genre'>
<option value='ALL'>All</option>
<option value='Horror'>Horror</option>
<option value='Anime'>Anime</option>
...
</select>

<select name='movie_cast'>
<option value='ALL'>All</option>
<option value='john'>john</option>
<option value='Katrina'>Blah blah Katrina</option>
...
</select>

If the user select "ALL" then, all the resuls will be displayed according to the selected options   AND again   If the user select "different" options then, all the resuls matcing from the database will be shown...   Tha database schema is shown below   | id | movie_name| movie_genre | movie_cast   | 1 | Hello Brother | Comedy        |  Salman Khan   | 2 | Blah blah       | ALL               | Blah blah   | 3 | ALL              |   ALL               | ALL   | 4 | Blah Blah      | Blah Blah       | ALL     * here "ALL" is a specific text which may or may not be present in the rows....   Thank u in advance....        

Hello all,

Trying to figure out how to display database results so that they are numbered (for editing and deletion purposes).  I want to be able to edit the message text and update the database information with the UPDATE command, but have not gotten that far yet.

Current problem is that I am returning no results.  Here is my script:

Code: [Select]
<!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">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>My Profile</title>
<link href="loginmodule.css" rel="stylesheet" type="text/css" />
</head>
<body>
<h1>My Profile </h1>
<a href="member-index.php">Home</a> | <a href="member-profile.php">My Profile</a> | Update Posts | <a href="logout.php">Logout</a>

<br /><br />

<?php
$subject = $_POST['subject'];
$message_text = $_POST['message_text'];

//Connect to mysql server
$link = mysql_connect('XXXXXX', 'XXXXXX', 'XXXXXX');
if(!$link)
{
   die('Failed to connect to server: ' . mysql_error());
}

//Select database
$db = mysql_select_db('ryan_iframe');
if(!$db)
{
   die("Unable to select database");
}

// validate incoming values
$subject = (isset($_GET['subject'])) ? (int)$_GET['subject'] : 0;
$message_text = (isset($_GET['message_text'])) ? (int)$_GET['message_text'] : 0;

ob_start();

$id = $_GET['SUBJECT'];
$query = sprintf( "
SELECT
   SUBJECT, MSG_TEXT, UNIX_TIMESTAMP(MSG_DATE) AS MSG_DATE
FROM
   FORUM_MESSAGE
WHERE
   SUBJECT = '$id'
ORDER BY
   MSG_DATE DESC",
   DB_DATABASE,
   DB_DATABASE,
   $subject,
   $subject);
$result = mysql_query($query) or die(mysql_error());
$num = mysql_numrows($result);
mysql_close();

$i = 0;
while ($i < $num)
{
   $subject = mysql_result($result, $i, "SUBJECT");
   $message_text = mysql_result($result, $i, "MSG_TEXT");
   
   echo '<div style="width: 400px;padding:20px;">';
   echo '<table border=0 width="400px">';
   echo '<tr>';
   echo '<td style="vertical-align:top;width:auto;">';
   echo 'Date: ';
   echo '</td>';
   echo '<td style="vertical-align:top;width:320px;">';
   echo date('F d, Y', $row['MSG_DATE']) . '</td>';
   echo '</tr>';
   echo '<tr>';
   echo '<td style="vertical-align:top;width:auto;">';
   echo 'Subject: ';
   echo '</td>';
   echo '<td style="vertical-align:top;width:320px;">';
   echo '<div>' . htmlspecialchars($row['SUBJECT']) . '</div>';
   echo '</td>';
   echo '</tr>';
   echo '<tr>';
   echo '<td style="vertical-align:top;width:auto;">';
   echo 'Message: ';
   echo '</td>';
   echo '<td style="vertical-align:top;width:320px;">';
   echo '<div>' . htmlspecialchars($row['MSG_TEXT']) . '</div>';
   echo '</td>';
   echo '</tr>';
   echo '<tr>';
   echo '<td style="vertical-align:top;width:auto;">';
   echo '</td>';
   echo '<td style="vertical-align:top;width:320px;text-align:center;">';
   echo '<form method="post">';
   echo '<input type="hidden" name="update" value="true" />';
   echo '<input type="submit" value="Update" />';
   echo '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;';
   echo '<input type="hidden" name="delete" value="true" />';
   echo '<input type="submit" value="Delete" />';
   echo '</form>';
   echo '</td>';
   echo '</tr>';
   echo '</table>';
   echo '<br />';
   echo '<hr />';
   echo '</div>';
   
      ++$i;
}

?>

</body>
</html>

Thanks in advance.

Ryan

Can someone please double check my search query to make sure it is the minimum and most efficient coding. My goal is an exact match search of field1 from my database table and list field1, field2, field3, field4 and field5 if the part number is in the database or no results if it is not. The query works as is but I have a feeling it could be done more efficiently or professionally and minimum work for my poor little server. Thanks in advance.
Code: [Select]
<?php
//connect to the database
include("./databaseconnect.php");
//get query
$q=$_GET['q'];
//convert query to uppercase & remove all spaces and special charactars
$q=strtoupper(preg_replace("/[^A-Za-z0-9]/","",$q));
//if blank query
if ($q == "")
{
echo "You did not enter a search term";
} else {
//exact match only query
$query  = "SELECT * FROM ".$dbtable." WHERE field1 like \"$q\"";
$result = mysql_query($query);
//if query returns no results
if(mysql_num_rows($result)==0)
{  
echo "<span class=\"noresults\">There were no results for your search</span>";
//display database results
} else {
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo "<span class=\'results\'>SEARCH RESULTS</span><br />
<span class=\'list\'>Part Number: ".$row['field1']."<br />
Manufacturer: ".$row['field2']."<br />
Cost per unit: ".$row['field3']."<br />
Warehouse Location: ".$row['field4']."<br />
Quantity Available: ".$row['field5']."<br />
Notes: ".$row['field6']."<br />
</span>";
}
}
}
?>

I'm trying to get a nickname system working for a little project, but I've run into a problem.

This code that I wrote here gets the users nickname from their username:

Code: (php) [Select]
$user = $_GET['user'];
@mysql_select_db("db") or die( "Unable to select database");

$query = mysql_query("SELECT `nickname` FROM `info` WHERE `username`='$user'");

       WHILE($rows = mysql_fetch_array($query)):
           $nickname = $rows['nickname'];
           echo $nickname;
        endwhile;
?>

But the problem is, if the user doesn't have a nickname then it just turns up blank. How can I check if the output of $nickname is blank and if it is then just return their normal name ($user)

Thanks

Oh, and how to you format php code to look like php code on these forums?

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=314391.0