php查询数据集的几种方式（mysql

<span>  1</span> <span>php
</span><span>  2</span> 
<span>  3</span>  
<span>  4</span> 
<span>  5</span> <span>/*</span>
<span>  6</span> 
<span>  7</span> <span> * 连接数据库
</span><span>  8</span> 
<span>  9</span> <span> * 固定不变的量，就直接用变量例如$mysql_host,变化的量例如$this->$mysql_dbname，表示当前调用者的dbname
</span><span> 10</span> 
<span> 11</span>  <span>*/</span>
<span> 12</span> 
<span> 13</span>  
<span> 14</span> 
<span> 15</span> <span>header</span>("Content-Type:text/html;charset=utf-8"<span>);
</span><span> 16</span> 
<span> 17</span>  
<span> 18</span> 
<span> 19</span>  <span>class</span><span> DbConn{
</span><span> 20</span> 
<span> 21</span>    
<span> 22</span> 
<span> 23</span>     <span>private</span> <span>$mysql_host</span>;<span>//</span><span>连接的主机</span>
<span> 24</span> 
<span> 25</span>     <span>private</span> <span>$mysql_user</span><span>;
</span><span> 26</span> 
<span> 27</span>     <span>public</span> <span>$mysql_pwd</span><span>;
</span><span> 28</span> 
<span> 29</span>     <span>private</span> <span>$mysql_language</span>;<span>//</span><span>数据库编码语言</span>
<span> 30</span> 
<span> 31</span>     <span>private</span> <span>$mysql_conn</span>;<span>//</span><span>Mysql连接对象</span>
<span> 32</span> 
<span> 33</span>     <span>private</span> <span>$db_select</span>; <span>//</span><span>选择的数据库对象</span>
<span> 34</span> 
<span> 35</span>    
<span> 36</span> 
<span> 37</span>     <span>public</span> <span>$mysql_dbname</span>;<span>//</span><span>选择要连接的数据库</span>
<span> 38</span> 
<span> 39</span>     <span>public</span> <span>$sqlstr</span>;<span>//</span><span>sql语句</span>
<span> 40</span> 
<span> 41</span>     <span>public</span> <span>$error</span>;<span>//</span><span>错误信息提示</span>
<span> 42</span> 
<span> 43</span>    
<span> 44</span> 
<span> 45</span>     <span>function</span><span> DbConn(){
</span><span> 46</span> 
<span> 47</span>         <span>$this</span>->mysql_host ="localhost"<span>;
</span><span> 48</span> 
<span> 49</span>         <span>$this</span>->mysql_user="root"<span>;
</span><span> 50</span> 
<span> 51</span>         <span>$this</span>->mysql_pwd="sasa"<span>;
</span><span> 52</span> 
<span> 53</span>         <span>$this</span>->mysql_language="UTF8"<span>;
</span><span> 54</span> 
<span> 55</span>         <span>$this</span>->mysql_conn=<span>NULL</span><span>;
</span><span> 56</span> 
<span> 57</span>         <span>$this</span>->db_select=<span>NULL</span><span>;
</span><span> 58</span> 
<span> 59</span>         <span>$this</span>->sqlstr=""<span>;
</span><span> 60</span> 
<span> 61</span>       
<span> 62</span> 
<span> 63</span> <span>    }
</span><span> 64</span> 
<span> 65</span>    
<span> 66</span> 
<span> 67</span>     <span>//</span><span>设置错误信息</span>
<span> 68</span> 
<span> 69</span>     <span>private</span>  <span>function</span> SetError(<span>$errormsg</span><span>)
</span><span> 70</span> 
<span> 71</span> <span>    {
</span><span> 72</span> 
<span> 73</span>         <span>$this</span>->error+=""+<span>$errormsg</span><span>;
</span><span> 74</span> 
<span> 75</span> <span>    }
</span><span> 76</span> 
<span> 77</span>    
<span> 78</span> 
<span> 79</span>     <span>public</span>  <span>function</span><span> ShowError()
</span><span> 80</span> 
<span> 81</span> <span>    {
</span><span> 82</span> 
<span> 83</span>         <span>return</span> <span>$this</span>-><span>error;
</span><span> 84</span> 
<span> 85</span> <span>    }
</span><span> 86</span> 
<span> 87</span>  
<span> 88</span> 
<span> 89</span>  
<span> 90</span> 
<span> 91</span>     <span>//</span><span>连接mysql</span>
<span> 92</span> 
<span> 93</span>     <span>public</span> <span>function</span><span> Connect()
</span><span> 94</span> 
<span> 95</span> <span>    {
</span><span> 96</span> 
<span> 97</span>         <span>$this</span>->mysql_conn = <span>mysql_connect</span>(<span>$this</span>->mysql_host, <span>$this</span>->mysql_user,<span>$this</span>->mysql_pwd) or <span>die</span>("mysql连接错误"); <span>//</span><span>mysql连接</span>
<span> 98</span> 
<span> 99</span>         <span>$this</span>->SetError(<span>mysql_error</span><span>());
</span><span>100</span> 
<span>101</span>         <span>//</span><span>选择连接的数据库名</span>
<span>102</span> 
<span>103</span>         <span>$this</span>->db_select = <span>mysql_select_db</span>(<span>$this</span>-><span>mysql_dbname</span>) or <span>die</span>("数据库无权限或数据库选择错误"<span>);
</span><span>104</span> 
<span>105</span>         <span>$this</span>->SetError(<span>mysql_error</span><span>());
</span><span>106</span> 
<span>107</span>         <span>mysql_query</span>("set names 'utf8'"); <span>//</span><span>编码转化</span>
<span>108</span> 
<span>109</span> <span>    }
</span><span>110</span> 
<span>111</span>  
<span>112</span> 
<span>113</span>     <span>//</span><span>关闭连接</span>
<span>114</span> 
<span>115</span>     <span>public</span> <span>function</span><span> CloseCon()
</span><span>116</span> 
<span>117</span> <span>    {
</span><span>118</span> 
<span>119</span>         <span>mysql_close</span>(<span>$this</span>-><span>mysql_conn);
</span><span>120</span> 
<span>121</span> <span>    }
</span><span>122</span> 
<span>123</span>    
<span>124</span> 
<span>125</span>     <span>//</span><span>查询方法</span>
<span>126</span> 
<span>127</span>     <span>public</span> <span>function</span><span> SelectAll()
</span><span>128</span> 
<span>129</span> <span>    {
</span><span>130</span> 
<span>131</span>          <span>$result</span> = <span>mysql_query</span>(<span>$this</span>->sqlstr,<span>$this</span>-><span>mysql_conn);
</span><span>132</span> 
<span>133</span>         <span>if</span>(<span>$result</span><span>)
</span><span>134</span> 
<span>135</span> <span>        {
</span><span>136</span> 
<span>137</span>         <span>//</span><span> $result_row = mysqli_fetch_array($result);
</span><span>138</span> 
<span>139</span> <span>        // mysql_fetch_row() 将返回结果集中的下一行，如果没有更多行则返回 FALSE。
</span><span>140</span> 
<span>141</span> <span>        // mysql_fetch_array() 将返回结果集中的键值或者数组，如果没有更多行则返回 FALSE。</span>
<span>142</span> 
<span>143</span>             <span>while</span>(<span>$result_row</span>= <span>mysql_fetch_array</span>(<span>$result</span><span>))
</span><span>144</span> 
<span>145</span> <span>            {
</span><span>146</span> 
<span>147</span>                 <span>$userid</span>=<span>$result_row</span>["userid"<span>];
</span><span>148</span> 
<span>149</span>                 <span>$username</span>=<span>$result_row</span>["username"<span>];
</span><span>150</span> 
<span>151</span>                 <span>$userpwd</span>=<span>$result_row</span>["userpwd"<span>];
</span><span>152</span> 
<span>153</span>                 <span>$useremail</span>=<span>$result_row</span>["useremail"<span>];
</span><span>154</span> 
<span>155</span>      
<span>156</span> 
<span>157</span>                 <span>echo</span> "<tr>"<span>;
</span><span>158</span> 
<span>159</span>                 <span>echo</span> "<td>用户名id：</td>
<td><span>$userid</span></td>"<span>;
</span><span>160</span> 
<span>161</span>                 <span>echo</span> "<td>用户名：</td>
<td><span>$username</span></td>"<span>;
</span><span>162</span> 
<span>163</span>                 <span>echo</span> "<td>密码：</td>
<td><span>$userpwd</span></td>"<span>;
</span><span>164</span> 
<span>165</span>                 <span>echo</span> "<td>邮箱：</td>
<td><span>$useremail</span></td>"<span>;
</span><span>166</span> 
<span>167</span>                 <span>echo</span> "</tr>"<span>;
</span><span>168</span> 
<span>169</span>                 <span>echo</span> ""<span>;
</span><span>170</span> 
<span>171</span>                
<span>172</span> 
<span>173</span>                 <span>//</span><span>mysqli_free_result($result);清除内存中的查询集</span>
<span>174</span> 
<span>175</span> <span>            }
</span><span>176</span> 
<span>177</span> <span>        }
</span><span>178</span> 
<span>179</span> <span>    }
</span><span>180</span> 
<span>181</span>    
<span>182</span> 
<span>183</span>     <span>//</span><span>大数据查询验证两种查询数据集的区别</span>
<span>184</span> 
<span>185</span>     <span>public</span> <span>function</span><span> Select()
</span><span>186</span> 
<span>187</span> <span>    {
</span><span>188</span> 
<span>189</span>         <span>//</span><span>分别取消 mysql_unbuffered_query和mysql_query查看效果</span>
<span>190</span> 
<span>191</span>       <span>$result</span> = <span>mysql_unbuffered_query</span>(<span>$this</span>->sqlstr,  <span>$this</span>-><span>mysql_conn);
</span><span>192</span> 
<span>193</span>         <span>//</span><span>$result = mysql_query($this->sqlstr,  $this->mysql_conn);</span>
<span>194</span> 
<span>195</span>        
<span>196</span> 
<span>197</span>          <span>if</span>(<span>$result</span><span>)
</span><span>198</span> 
<span>199</span> <span>        {
</span><span>200</span> 
<span>201</span>         <span>//</span><span> $result_row = mysqli_fetch_array($result);
</span><span>202</span> 
<span>203</span> <span>        // mysql_fetch_row() 将返回结果集中的下一行，如果没有更多行则返回 FALSE。
</span><span>204</span> 
<span>205</span> <span>        // mysql_fetch_array() 将返回结果集中的键值或者数组，如果没有更多行则返回 FALSE。</span>
<span>206</span> 
<span>207</span>             <span>while</span>(<span>$result_row</span>= <span>mysql_fetch_array</span>(<span>$result</span><span>))
</span><span>208</span> 
<span>209</span> <span>            {
</span><span>210</span> 
<span>211</span>                 <span>$userid</span>=<span>$result_row</span>["userid"<span>];
</span><span>212</span> 
<span>213</span>                 <span>$username</span>=<span>$result_row</span>["username"<span>];
</span><span>214</span> 
<span>215</span>                 <span>$userpwd</span>=<span>$result_row</span>["userpwd"<span>];
</span><span>216</span> 
<span>217</span>                 <span>$useremail</span>=<span>$result_row</span>["useremail"<span>];
</span><span>218</span> 
<span>219</span>      
<span>220</span> 
<span>221</span>                 <span>echo</span> "<tr>"<span>;
</span><span>222</span> 
<span>223</span>                 <span>echo</span> "<td>用户名id：</td>
<td><span>$userid</span></td>"<span>;
</span><span>224</span> 
<span>225</span>                 <span>echo</span> "<td>用户名：</td>
<td><span>$username</span></td>"<span>;
</span><span>226</span> 
<span>227</span>                 <span>echo</span> "<td>密码：</td>
<td><span>$userpwd</span></td>"<span>;
</span><span>228</span> 
<span>229</span>                 <span>echo</span> "<td>邮箱：</td>
<td><span>$useremail</span></td>"<span>;
</span><span>230</span> 
<span>231</span>                 <span>echo</span> "</tr>"<span>;
</span><span>232</span> 
<span>233</span>                 <span>echo</span> ""<span>;
</span><span>234</span> 
<span>235</span>                 <span>echo</span> "处理过程中数据集总行数获取:"<span>;
</span><span>236</span> 
<span>237</span>                 <span>//</span><span>mysql_unbuffered_query查询的结果集中，mysql_fetch_array处理过程中，不可以使用mysql_num_rows获取结果集行数</span>
<span>238</span> 
<span>239</span>                  <span>echo</span> <span>mysql_num_rows</span>(<span>$result</span><span>);
</span><span>240</span> 
<span>241</span>                  <span>echo</span> ""<span>;
</span><span>242</span> 
<span>243</span>               
<span>244</span> 
<span>245</span> <span>            }
</span><span>246</span> 
<span>247</span>             <span>mysql_data_seek</span>(<span>$result</span>,1);<span>//</span><span>把指针指向数据集最开始
</span><span>248</span> 
<span>249</span> <span>            //mysql_unbuffered_query查询的结果集中，mysql_fetch_array处理过程完成后，可以使用mysql_num_rows获取结果集行数</span>
<span>250</span> 
<span>251</span>             <span>echo</span> "处理完成后,数据集总行数获取："<span>;
</span><span>252</span> 
<span>253</span>             <span>echo</span> <span>mysql_num_rows</span>(<span>$result</span><span>);
</span><span>254</span> 
<span>255</span>             <span>echo</span> ""<span>;
</span><span>256</span> 
<span>257</span>            
<span>258</span> 
<span>259</span>            
<span>260</span> 
<span>261</span>             <span>//</span><span>再次操作mysql_unbuffered_query查询的结果集，数据集为空不输出</span>
<span>262</span> 
<span>263</span>              <span>while</span>(<span>$result_row</span>= <span>mysql_fetch_array</span>(<span>$result</span><span>))
</span><span>264</span> 
<span>265</span> <span>            {
</span><span>266</span> 
<span>267</span>                 <span>$userid</span>=<span>$result_row</span>["userid"<span>];
</span><span>268</span> 
<span>269</span>                 <span>$username</span>=<span>$result_row</span>["username"<span>];
</span><span>270</span> 
<span>271</span>                 <span>$userpwd</span>=<span>$result_row</span>["userpwd"<span>];
</span><span>272</span> 
<span>273</span>                 <span>$useremail</span>=<span>$result_row</span>["useremail"<span>];
</span><span>274</span> 
<span>275</span>               
<span>276</span> 
<span>277</span>                 <span>echo</span> "<tr>"<span>;
</span><span>278</span> 
<span>279</span>                 <span>echo</span> "<td>用户名id：</td>
<td><span>$userid</span></td>"<span>;
</span><span>280</span> 
<span>281</span>                 <span>echo</span> "<td>用户名：</td>
<td><span>$username</span></td>"<span>;
</span><span>282</span> 
<span>283</span>                 <span>echo</span> "<td>密码：</td>
<td><span>$userpwd</span></td>"<span>;
</span><span>284</span> 
<span>285</span>                 <span>echo</span> "<td>邮箱：</td>
<td><span>$useremail</span></td>"<span>;
</span><span>286</span> 
<span>287</span>                 <span>echo</span> "</tr>"<span>;
</span><span>288</span> 
<span>289</span>                 <span>echo</span> ""<span>;
</span><span>290</span> 
<span>291</span>                
<span>292</span> 
<span>293</span>                 <span>//</span><span>mysqli_free_result($result);清除内存中的查询集</span>
<span>294</span> 
<span>295</span> <span>            }
</span><span>296</span> 
<span>297</span>              <span>echo</span> "再次处理后行数为："<span>;
</span><span>298</span> 
<span>299</span>               <span>echo</span> <span>mysql_num_rows</span>(<span>$result</span><span>);
</span><span>300</span> 
<span>301</span>                <span>mysql_free_result</span>(<span>$result</span><span>);
</span><span>302</span> 
<span>303</span>          
<span>304</span> 
<span>305</span>         }<br>306　　　　}<br>307　　　}