PHP - Can You Use Php For Loops Inside Of A Mysql Query

Here's the code, I had used the query in the header, and now I'm using it to display the links in a table. What happens is that it only returns the first row and not the second third or fourth rows. When it does display the first row, it'll display it a lot of times. Here it is

Code: [Select]
<?php
    if($_GET['place']==addnetwork){
        //display current networks 
        echo "<table border=1>";
        echo "<tr><td>Network Name</td><td>URL</td></tr>";
        
        while ($query1 = mysql_fetch_assoc(mysql_query("select name,url from s_links"))) {
            $networkname = $query1['name'];
            $networkurl = $query1['url'];

            
            echo "<tr><td>$networkname</td><td>$networkurl</td></tr>";
            
        }
        echo "</table>";
    }
?>

Hi,

I'm trying to create a script that imports a CSV file but gives the user 3 options:

1. If Exists Update
2. If Exists Keep Both
3. If Exists Ignore New

The keep both is easy, I just insert everything without checks.

However, the other 2 are a little bit tricky (in my mind anyway). I've been told I could create loops and that there isn't any easy way to do this with MySQL. I cannot use ON DUPLICATE KEY because the column that I'm checking cannot be unique (because I have the keep both option).

Is it efficient in PHP to create a loop that checks each row to see if it exists? I'm a little hesitant because the files could be big and I'm not sure how efficient PHP is with CPU and memory resources.

The App I'm making is designed to run in a business environment but it may run on servers that are handling more than one thing (not just web services).

If anyone could give me a few pointers I'd be most grateful.

Thanks.

hey guys, in my script i displays status' the users have posted. However when the users status' got over like 20, i noticed it was affected load times greatly as lots of SQL is looping.

Code: [Select]
<?php
   $sql =  sqlcount(mysql_query("SELECT * FROM statuses LEFT JOIN users ON statuses.userid = users.id ORDER BY statusid DESC"));
while ($row = mysql_fetch_assoc($sql)) {
$status_id = htmlspecialchars($row['statusid']);
$status = htmlspecialchars($row['status']);
$time_posted = htmlspecialchars($row['time_posted']);
$status_userid = htmlspecialchars($row['userid']);
$sql2 = sqlcount(mysql_query("SELECT username,avatar_url FROM users WHERE id=$status_userid LIMIT 1"));
while ($row2=mysql_fetch_array($sql2)){
$usernamestatus = $row2['username'];
$avatar_url = $row2['avatar_url'];
if (empty($avatar_url)) {
$avatar = "<img src='$directory_self/images/default_avatar.gif' height='50px' width='50px' />";
}else{
$avatar = "<img src='$directory_self/photos/$usernamestatus/$avatar_url' height='50px' width='50px' />";
}
echo '<div id="content">';
echo "<br />";
echo $avatar;
echo "<a href=\"profile.php?id=$status_userid\">". $usernamestatus ."</a>";
echo "<br />";
  $status = str_replace(array_keys($bbcode), array_values($bbcode), $status);
echo $status;
if($userid == $status_userid){
echo " <span class=x><a href=delete_status.php?id=$status_id>x</a></span>";
}
echo "<br />";
echo format_date($time_posted);
echo "<br />";
echo "<br />";
?>
<div id="like<?php echo"$status_id"; ?>"><a href="#" style="text-decoration: none" class="like" id="<?php echo"$status_id"; ?>"><span class="like_b"> <?php echo LANG_LIKE; ?> </span></a></div>
  <div id="unlike<?php echo"$status_id"; ?>" style="display:none"><span class="youlike_b"> <?php echo"$user"; echo  LANG_LIKES_THIS; ?> </span><a href="#" class="unlike" id="<?php echo"$status_id"; ?>"><span class="unlike_b"> <?php echo LANG_UNLIKE; ?> </span></a></div>
<?php
echo "</div>";
}
}
?>
Is there a way to shorten this code and make it run faster, without as many queries?
Cheers

Here is my code:
// Start MySQL Query for Records
$query = "SELECT codes_update_no_join_1b" .
"SET orig_code_1 = new_code_1,
       orig_code_2 = new_code_2" .
"WHERE concat(orig_code_1, orig_code_2) = concat(old_code_1, old_code_2)";
$results = mysql_query($query) or die(mysql_error());
// End MySQL Query for Records


This query runs perfectly fine when run direct as SQL in phpMyAdmin, but throws this error when running in my script??? Why is this???

Code: [Select]
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= new_code_1, orig_code_2 = new_code_2WHERE concat(orig_code_1, orig_c' at line 1

If you also have any feedback on my code, please do tell me. I wish to improve my coding base.

Basically when you fill out the register form, it will check for data, then execute the insert query. But for some reason, the query will NOT insert into the database. In the following code below, I left out the field ID. Doesn't work with it anyways, and I'm not sure it makes a difference.

Code:

  Code: [Select]
mysql_query("INSERT INTO servers (username, password, name, type, description, ip, votes, beta) VALUES ($username, $password, $name, $server_type, $description, $ip, 0, 1)");
Full code:

Code: [Select]
<?php
include_once("includes/config.php");
 
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title><? $title; ?></title>
<meta http-equiv="Content-Language" content="English" />
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<link rel="stylesheet" type="text/css" href="style.css" media="screen" />
</head>
<body>
 
<div id="wrap">
 
<div id="header">
<h1><? $title; ?></h1>
<h2><? $description; ?></h2>
</div>
 
<? include_once("includes/navigation.php"); ?>
 
<div id="content">
<div id="right">
<h2>Create</h2>
<div id="artlicles">
<?php
 
if(!$_SESSION['user'])
{
 
        $username = mysql_real_escape_string($_POST['username']);
        $password = mysql_real_escape_string($_POST['password']);
        $name = mysql_real_escape_string($_POST['name']);
        $server_type = mysql_real_escape_string($_POST['type']);
        $description = mysql_real_escape_string($_POST['description']);
 
        if(!$username || !$password || !$server_type || !$description || !$name)
        {

        echo "Note: Descriptions allow HTML. Any abuse of this will result in an IP and account ban. No warnings!<br/>All forms are required to be filled out.<br><form action='create.php' method='POST'><table><tr><td>Username</td><td><input type='text' name='username'></td></tr><tr><td>Password</td><td><input type='password' name='password'></td></tr>";
        echo "<tr><td>Sever Name</td><td><input type='text' name='name' maxlength='35'></td></tr><tr><td>Type of Server</td><td><select name='type'>
       
        <option value='Any'>Any</option>
        <option value='PvP'>PvP</option>
        <option value='Creative'>Creative</option>
        <option value='Survival'>Survival</option>
        <option value='Roleplay'>RolePlay</option>
       
        </select></td></tr>
       
        <tr><td>Description</td><td><textarea maxlength='1500' rows='18' cols='40' name='description'></textarea></td></tr>";
        echo "<tr><td>Submit</td><td><input type='submit'></td></tr></table></form>";
        }
        elseif(strlen($password) < 8)
        {
        echo "Password needs to be higher than 8 characters!";
        }
        elseif(strlen($username) > 13)
        {
                echo "Username can't be greater than 13 characters!";
        }
        else
        {
                $check1 = mysql_query("SELECT username,name FROM servers WHERE username = '$username' OR name = '$name' LIMIT 1");
               
                if(mysql_num_rows($check1) < 0)
                {
                        echo "Sorry, there is already an account with this username and/or server name!";
                }
                else
                {
                        $ip = $_SERVER['REMOTE_ADDR'];

                        mysql_query("INSERT INTO servers (username, password, name, type, description, ip, votes, beta) VALUES ($username, $password, $name, $server_type, $description, $ip, 0, 1)");
                        echo "Server has been succesfully created!";
                }
        }
       
}
else
{
        echo "You are currently logged in!";
}
 
?>
</div>
</div>
 
<div style="clear: both;"> </div>
</div>
 
<div id="footer">
<a href="http://www.templatesold.com/" target="_blank">Website Templates</a> by <a href="http://www.free-css-templates.com/" target="_blank">Free CSS Templates</a> - Site Copyright MCTop
</div>
</div>
 
</body>
</html>

Here is my query
Code: [Select]
SELECT id,username,star,color,actions from users WHERE actions >= 1 ORDER BY actions DESC
actions field value is "213|1336456267"

how can I use mysql to explode so it only reads the first literation of the exploded values, like 213?

Is this why people always say you shouldn't store exploded variables?

Theres gotta be a way tho right?

in other words; is there a mysql function that exploded that actions variable in the query with a | to read only the "213" ?

I'm restarting this under a new subject b/c I learned some things after I initially posted and the subject heading is no longer accurate.

What would cause this behavior - when I populate session vars from a MYSQL query, they stick, if I populate them from an MSSQL query, they drop.

It doesn't matter if I get to the next page using a header redirect or a form submit.  I have two session vars I'm loading from a MYSQL query and they remain, the two loaded from MSSQL disappear. 

I have confirmed that all four session vars are loading ok initially and I can echo them out to the page, but when the application moves to next page via redirect or form submit, the two vars loaded from MSSQL are empty. 

Any ideas?

Hello to all,

I have problem figuring out how to properly display data fetched from MySQL database in a HTML table.

In the below example I am using two while loops, where the second one is nested inside first one, that check two different expressions fetching data from tables found in a MySQL database. The second expression compares the two tables IDs and after their match it displays the email of the account holder in each column in the HTML table.

The main problem is that the 'email' row is displayed properly while its while expression is not nested and alone(meaning the other data is omitted or commented out), but either nested or neighbored to the first while loop, it is displayed horizontally and the other data ('validity', 'valid_from', 'valid_to') is not displayed.'

Can someone help me on this, I guess the problem lies in the while loop?
Here is part of the HTML code:

<thead>
 <tr>
 <th data-column-id="id" data-type="numeric">ID</th>
 <th data-column-id="email">Subscriber's Email</th>
 <th data-column-id="validity">Validity</th>
<th data-column-id="valid_from">Valid From</th>
 <th data-column-id="valid_to">Valid To</th>
</tr>
</thead>

Here is part of the PHP code:

 

    <?php
    while($row = $stmt->fetch(PDO::FETCH_ASSOC))
    {
        echo '
            <tr>
                <td>'.$row["id"].'</td>
        ';
        while ($row1 = $stmt1->fetch(PDO::FETCH_ASSOC)) {
        echo '
                <td>'.$row1["email"].'</td>
        ';
        }
        if($row["validity"] == 1) {
            echo '<td>'.$row["validity"].' month</td>';
        }else{
            echo '<td>'.$row["validity"].' months</td>';
        }
        echo '  <td>'.$row["valid_from"].'</td>
                <td>'.$row["valid_to"].'</td>
        </tr>';
    }
    ?>

P.S. Please know that I have purposely omitted the connection part and the methods in the class that are used in this code to keep the spot of the problem as clean as possible. If you need me to rephrase the question, please ask.

Thank you.

How can I insert PHP code inside MySQL DB? I have mixed HTML and PHP code like this:
<div class="rightpanel">
   <div class="hltred">
      <div class="hlthead">
         <p><?php echo $pages->naslov; ?></p>
      </div>
      <div class="hltcontent">
         <p>
            ovde treba da ide loop sa svi podlinkovi za taj glavni koji je aktivan</p>
      </div>
   </div>
</div>,
but when i insert it into DB i get this code (I use htmlspecialchars function to store):
<div class="rightpanel">
   <div class="hltred">
      <div class="hlthead">
         <p>
<!--?php echo $pages--->            naslov; ?&gt;</p>
      </div>
      <div class="hltcontent">
         <p>
            ovde treba da ide loop sa svi podlinkovi za taj glavni koji je aktivan</p>
      </div>
   </div>
</div>

Hi,
So as you might (or might not) have guessed I need to open/close new connections inside my functions.

Here's my current code:

Code: [Select]
<?PHP

/**
 * This function checks the ban status of the account.
 * @return      1 if banned
 */
function checkBan()
{
    mysql_close($con);
    require("./includes/wow.php");
    $result = mysql_query("SELECT * FROM wow_logon.accounts WHERE forum_acc= '.$user->data['user_id'].'");
    $row = mysql_fetch_array($result);
    if($row["banned"] == "1")
    {
        return 1;
        $ban_reason = $row["banreason"];
    }
    //$user->data['user_id']
     
    mysql_close($connect);       
    require("./includes/config.php");
}


?>
When trying to use the function:

checkBan();

if(checkban() == "1")
{
    echo'function works, and returns 1. Ban reason: '.$ban_reason.' ';
}

This gives a pretty good idea of what I try to accomplish I hope, if not, an explanation is below.
This code unfortunately returns:
Code: [Select]
[phpBB Debug] PHP Warning: in file /home/fusion/public_html/includes/functions_user.php on line 9: mysql_close() expects parameter 1 to be resource, null given
[phpBB Debug] PHP Warning: in file /home/fusion/public_html/includes/functions_user.php on line 12: mysql_fetch_array() expects parameter 1 to be resource, boolean given
[phpBB Debug] PHP Warning: in file /home/fusion/public_html/includes/functions_user.php on line 9: mysql_close() expects parameter 1 to be resource, null given
[phpBB Debug] PHP Warning: in file /home/fusion/public_html/includes/functions_user.php on line 12: mysql_fetch_array() expects parameter 1 to be resource, boolean given

Line 9 & 12:
mysql_close($con);
$row = mysql_fetch_array($result);


Explanation:
What I want to do is that I have a user account panel.
When the user log in, I want to call a function to check if the user is banned. If the user is banned then we have returned 1, and display some banned message, followed by the ban reason.
P.S. is it possible to use stored variables inside a function?

When I do this, I connect to another sql server, by first closing the existing connection (if any) and open a new one to execute my statements. Then close that connection and resume the old one.

Is it possible to have a loop inside a mysql select statement? If yes, can someone please give me an idea.

An affiliate marketer refers some products which are stored in a simple array.
My website creates a JSON variable of all products sold.

$all_items_sold = '{"7777":{"item":"hammer","price":"4.99"},"8888":{"item":"nail","price":"1.99"},"9999":{"item":"apple","price":"2.00"}}';
$referred_by_Affiliate = array('1234','8888','7777');

So, out of all the 3 items that sold, only 2 of them were referred by the affiliate marketer.
DESIRED EFFECT: insert this product 8888: $1.99 and insert this product 7777: $4.99 into mysql.

Currently I do this:
1.) foreach loop,
2.) use strpos to see if it's in the raw JSON variable.
3.) If there, I use preg_match to find the price.
4.) Do a mySQL insert while still in the foreach loop.

Is there a "best practices" way to accomplish this?
I'm guessing there is a "one liner" so I don't have to do a foreach loop and using strpos.
And I'm guessing there is a way to do multiple mySQL inserts all at once with just one line of code, instead of from inside a foreach loop.
This probably can all be accomplished with just one line of code total, error trapping included 😁

Thank you.
 

Edited July 28 by ChenXiu

The Script:

<form method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>">
    <input type="text" name="hashtags" />
    <input type="submit" name="submit" />

</form>
<?php

include("connect.php");

?>
<?php

if(isset($_POST['submit'])){

    $hashtags = explode(", ", $_POST['hashtags']);

    for($i= 0; $i < count($hashtags); $i++){

        $tqs = "SELECT `id` FROM `hashtags_two` WHERE `hashtags` = '" . $hashtags[$i] . "'";
        $tqr = mysqli_query($dbc, $tqs) or die(mysqli_error($dbc));

    }

    $fetch_array = array();
    while($row = mysqli_fetch_array($tqr)){
        $fetch_array[] = $row['id'];
    }

}

Hey, sorry for these fundamental array questions lately, I am not going to ask much more when it comes to this.   The hashtags come from the submit form (separated by commas) and get stored inside an array with the "explode()" function. The script should select the ID numbers of the hashtags from the table.   With the script above only the last row gets inserted into $fetch_array inside the while loop.   When the $fetch_array variable is printed on screen then only one ID number can be seen inside the array:

Array ( [0] => 24 ) 

How can I have the other ID numbers too?

Hey guys, I'm facing an issue compiling the above stack from a source code inside lxc using centos 6.5 as a domain OS.     [lxc@lxc1 httpd-2.4.9]$ ./configure --with-included-apr
checking for chosen layout... Apache
checking for working mkdir -p... yes
checking for grep that handles long lines and -e... /bin/grep
checking for egrep... /bin/grep -E
checking build system type... x86_64-unknown-linux-gnu
checking host system type... x86_64-unknown-linux-gnu
checking target system type... x86_64-unknown-linux-gnu
configu
configu Configuring Apache Portable Runtime library...
configu
configuring package in srclib/apr now
checking build system type... x86_64-unknown-linux-gnu
checking host system type... x86_64-unknown-linux-gnu
checking target system type... x86_64-unknown-linux-gnu
Configuring APR library
Platform: x86_64-unknown-linux-gnu
checking for working mkdir -p... yes
APR Version: 1.5.1
checking for chosen layout... apr
checking for gcc... gcc
checking whether the C compiler works... yes
checking for C compiler default output file name... a.out
checking for suffix of executables...
checking whether we are cross compiling... configu error: in `/home/lxc/httpd-2.4.9/srclib/apr':
configu error: cannot run C compiled programs.
If you meant to cross compile, use `--host'.
See `config.log' for more details
configure failed for srclib/apr
      This problem has been detected by me when I replaced my desktop machine with new one and installed a centOS again. This such a problem never happened before using my old machine with the same version of OS and libvirt. Just to be clear, a new selinux policy into a "domain machine" has been created to be able to use the "dbus daemon" to all containers and if I try to complile this stack from source using the "domain os" this problem never happens at all. All "Development tools" is installed to this particular container, in case someone asks me why I get the following error message - "configu error: cannot run C compiled programs" Any ideas?    
Edited by jazzman1, 08 June 2014 - 01:37 PM.

Hello!

Please help... I am trying to use the script below to get results from a mysql database based on a query of the form fields (the names of which are displayed near the top of the script as POST items)

When a location or age etc. is entered into the form, I want the script to search for records which meet those criteria. At the moment the script works but only does so if all the values are entered to match what is in the database. e.g. if the location england and the age 22 was entered into the form, and that matched the value in the database, then at the moment, the script will display the result, but if only the location is entered in the form without any value for age/genre etc. then no results are displayed.

Any help would be very welcome as I have search high and low for a solution on google... which doesn't seem to exist...

I'm not that experienced with php/mysql but am learning on the job so any helpful prompts as to terms etc. would help!

Thanks!

Lewis

<?php 

if($_POST) 
{ 
$searchage = $_POST['searchage'];
$searchlocation = $_POST['searchlocation'];
$searchgenre = $_POST['searchgenre']; 
$searchinstrument = $_POST['searchinstrument']; 
$searchexperience = $_POST['searchexperience']; 
 
// Connects to your Database 
mysql_connect("localhost", "user", "pass") or die(mysql_error()); 
mysql_select_db("DB") or die(mysql_error()); 

$query = mysql_query("SELECT * FROM table_user WHERE userage = '".$searchage."' AND userlocation = '".$searchlocation."' AND usergenre = '".$searchgenre."' AND userinstrument = '".$searchinstrument."' AND userexperience = '".$searchexperience."'") or die(mysql_error()); 

$num = mysql_num_rows($query); 

echo "$num results found!<br>"; 

while($result = mysql_fetch_assoc($query)) 
{ 
$username = $result['username']; 
$useremail = $result['useremail'];
$userage = $result['userage'];
$userlocation = $result['userlocation'];
$usergenre = $result['usergenre'];
$userinstrument = $result['userinstrument']; 
$userexperience = $result['userexperience']; 
$userbiography = $result['userbiography']; 

echo "
Name: $username<br> 
Email: $useremail<br>
Age: $userage<br>
Location: $userlocation<br>
Gen  $usergenre<br>
Instrument: $userinstrument<br>
Experience: $userexperience<br>
Biography: $userbiography<br><br>       
"; 
} 


} 

?>